From: Pranab Dhar <pkdhar@nipsco.com>
To: pgsql-sql@postgresql.org
Subject: TimeStamp Anomaly - Any reasons ?
Date: Mon, 17 Apr 2000 12:24:59 -0500
Message-ID: <38FB48EB.F2DE24CD@nipsco.com> (raw)
Hi PGSQL Users,
I have come across a situation where I want to insert a TimeStamp into a
table.The TimeStamp is
generated by a java program in the format 'YYYY-MM-DD HH:MI:SS.MS' e.g.
'2000-04-17 11:41:05.0'.
When I tried inserting a time from psql user interface I get a 1 hour
difference.
I have set TZ=CDT ,PGTZ=CDT.I am running postgres6.5.3 on NT4.0. Here
are the steps
__________________________________________________________________________________________
testdb=> \d test
Table = test
+----------------------------------+----------------------------------+-------+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-------+
| updt_dt | timestamp
| 4 |
+----------------------------------+----------------------------------+-------+
testdb=> insert into test values(current_timestamp);
INSERT 24864 1
testdb=> select * from test;
updt_dt
----------------------
2000-04-17 11:41:05-05
(1 row)
testdb=> insert into test values('2000-04-17 11:41:05');
INSERT 24865 1
testdb=> select * from test;
updt_dt
----------------------
2000-04-17 11:41:05-05
2000-04-17 12:41:05-05 <== new row
(2 rows)
testdb=> show time zone
testdb-> ;
NOTICE: Time zone is CDT
SHOW VARIABLE
testdb=>
___________________________________________________________________________________________
Now I try to retrieve the same rows from a java program .I get the
following result:-
D:\>java Table
TimeZone:America/Chicago
TimeZone:CDT
2000-04-17 11:41:05+01
2000-04-17 12:41:05+01
The code which does this is :-
while(rs.next())
{
objname = rs.getString("updt_dt");
System.out.println( objname);
}
If I user this code
objname = rs.getTimestamp("updt_dt").toString();
I get this result.
2000-04-17 05:41:05.0
2000-04-17 06:41:05.0
Now If I try to insert '2000-04-17 11:41:05' using the java program
st.executeUpdate("insert into test values('2000-04-17 11:41:05')");
the database shows
testdb=> select * from test;
updt_dt
----------------------
2000-04-17 11:41:05-05
2000-04-17 12:41:05-05
2000-04-17 06:41:05-05 <== newly inserted row
(3 rows)
and if I had inserted 2000-04-17 06:41:05.0 it would have been off by 5
hours like this
testdb=> select * from test;
updt_dt
----------------------
2000-04-17 11:41:05-05
2000-04-17 12:41:05-05
2000-04-17 06:41:05-05
2000-04-17 01:41:05-05 <== new row
(4 rows)
I expect a timestamp to be retrieved and stored by a jdbc program
without any change as it is.Problem is I lost 5 hours on the timestamp
when I try to do that.I wanted the code to be database independent but
it looks like there is a timezone conversion involved as the
java.sql.Timestamp doesnt allow any timezone information in it.
I will appreciate any input on this problem.
Thanks
PKD
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: pkdhar@nipsco.com
Subject: Re: TimeStamp Anomaly - Any reasons ?
In-Reply-To: <38FB48EB.F2DE24CD@nipsco.com>
References: <38FB48EB.F2DE24CD@nipsco.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox