Thread: postgres timestamp data errors

Hi,

We have a problem with postgres handling of certain timestamps where the timestamps can go into postgres through jdbc interface but cannot be retrieved back. It appears to be partly postgres-jdbc and partly postgres server problem.

Here is an example:


db=# show timezone;
 TimeZone
----------
 Eire
(1 row)

db=# select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
(1 row)

db=# create table test_table (invoice_date timestamp with time zone);
CREATE TABLE
db=# insert into test_table values('1912-03-14 00:00:00.000000 -0025');
INSERT 0 1
db=# select * from test_table;
         invoice_date
------------------------------
 1912-03-13 23:59:39-00:25:21
(1 row)


Note that inserted timestamp and stored timestamps are logically equivalent. However, why does SELECT show the timestamp in a timezone offset with minute precision? Why doesn't psql show the stored timestamp in session timezone? postgres jdbc driver throws an exception when parsing that timestamp, and the application cannot get that data back.

(If you want to how why the app would insert a timestamp with -0025 TZ in the first place, that's the timestamp postgres jdbc driver inserts when the app tries to insert a java timestamp of 1912-03-14.)

Any ideas what's happening?

thanks

maxxedev maxxedev wrote:

> db=# show timezone;
>  TimeZone
> ----------
>  Eire
> (1 row)

> db=# insert into test_table values('1912-03-14 00:00:00.000000 -0025');
> INSERT 0 1
> db=# select * from test_table;
>  invoice_date
> ------------------------------
>  1912-03-13 23:59:39-00:25:21
> (1 row)
>
>
> Note that inserted timestamp and stored timestamps are logically
> equivalent. However, why does SELECT show the timestamp in a timezone
> offset with minute precision?

Because in 1913, that was the offset between Dublin time and GMT.

http://www.velocityreviews.com/forums/t372817-timestamp-and-the-eire-timezone.html

-Kevin