TimeStamp Anomaly - Any reasons ?

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

Browse pgsql-sql by date

  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