pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



Message-ID: <38FB48EB.F2DE24CD@nipsco.com>
Permalink:  ../38FB48EB.F2DE24CD@nipsco.com/
Also on:    postgresql.org/message-id/38FB48EB.F2DE24CD@nipsco.com

 · 

reply

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