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
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... |