postgres timestamp data errors

From: maxxedev maxxedev <maxxedev(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: postgres timestamp data errors
Date: 2012-11-30 09:29:38
Message-ID: CABUXMx7YUyNnQcvdf0ygf1r92fgteoYm632XhYqJBBYqTMueeQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Vik Reykja 2012-11-30 10:18:10 Re: proposal: fix corner use case of variadic fuctions usage
Previous Message Albe Laurenz 2012-11-30 09:08:26 Re: About aggregates...