From: | Pranab Dhar <pkdhar(at)nipsco(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | TimeStamp Anomaly - Any reasons ? |
Date: | 2000-04-17 17:24:59 |
Message-ID: | 38FB48EB.F2DE24CD@nipsco.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Lewis | 2000-04-17 22:26:35 | Full Text Searching |
Previous Message | dbms dbms | 2000-04-17 16:30:14 | For ORACLE experts |