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 по дате отправления:

Предыдущее
От: Angel Manuel Diaz Aunion
Дата:
Сообщение: function
Следующее
От: Andy Lewis
Дата:
Сообщение: Full Text Searching