TimeStamp Anomaly - Any reasons ?
От | Pranab Dhar |
---|---|
Тема | TimeStamp Anomaly - Any reasons ? |
Дата | |
Msg-id | 38FB48EB.F2DE24CD@nipsco.com обсуждение исходный текст |
Список | 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
В списке pgsql-sql по дате отправления: