Re: Timezone conversion woes

Christian,

Did you resolve this ?

Without looking at the code, I am thinking that using an absolute
long for the timestamp might
be the problem. I generally create a calendar and get the date I want
out of it ?

Dave
On 15-Jul-05, at 7:04 PM, Christian Cryder wrote:

> And then there's this. If I change my insert code to use dynamically
> generated SQL via Statement, rather than PreparedStatement, like this:
>
>         //insert some sample data
>         t = new Timestamp(1112511962000L);        //2005-04-03
> 00:06:02
>         System.out.println("inserting: "+sdf.format(t)+" (millis:
> "+t.getTime()+")");
>         stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
>         t = new Timestamp(1112520583000L);        //2005-04-03
> 02:29:43
>         System.out.println("inserting: "+sdf.format(t)+" (millis:
> "+t.getTime()+")");
>         stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
>         t = new Timestamp(1112522529000L);        //2005-04-03
> 03:02:09
>         System.out.println("inserting: "+sdf.format(t)+" (millis:
> "+t.getTime()+")");
>         stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
>         if (!conn.getAutoCommit()) conn.commit();
>
> the data goes in correctly (no mungin on the last two dates). I get
> the following output after the inserts...
>
> [UID]:58 [TrxTime]:2005-04-03 00:06:02.000 (millis: 1112511962000)
> [UID]:59 [TrxTime]:2005-04-03 02:29:43.000 (millis: 1112520583000)
> [UID]:60 [TrxTime]:2005-04-03 03:02:09.000 (millis: 1112522529000)
>
> So it appears to me there is a bug, either in the PreparedStatement
> code or in the way the DB handles dates set via prepared stmts.
>
> Can anyone verify or comment on this? Any suggestions as to how we
> might fix it, or where I should look? I have no problem trying to
> patch the JDBC code, but I could use a few pointers about where to
> look first...
>
> thanks,
> Christian
>
>
>
> On 7/15/05, Christian Cryder <c.s.cryder@gmail.com> wrote:
>
>> And just a little bit more information. I downloaded the jdbc source,
>> and poked around a little bit to see if I could determine exactly
>> what's going across the wire. I get this...
>>
>> FE=> Bind(stmt=null,portal=null,$1=<2005-04-03 00:06:02.000000-0700>)
>> FE=> Bind(stmt=null,portal=null,$1=<2005-04-03 02:29:43.000000-0700>)
>> FE=> Bind(stmt=null,portal=null,$1=<2005-04-03 03:02:09.000000-0700>)
>>
>> It would appear to me from this that the data is going out of the
>> JDBC
>> drivers correctly, and that if the dates are getting modified (which
>> they are), it's Postgres that's doing it. Can anyone confirm,
>> deny, or
>> correct my thinking here?
>>
>> tia,
>> Christian
>>
>>
>>
>> On 7/15/05, Christian Cryder <c.s.cryder@gmail.com> wrote:
>>
>>> And just in case that example wasn't clear enough, I've tried using
>>> rs.getTimestamp(i) or rs.getTimestamp(i, cal) instead of
>>> rs.getObject(i). Neither of those have any effect.
>>>
>>> The heart of the problem here seems to be that the millis value is
>>> really getting changed on the way to the DB...
>>>
>>> inserting: 2005-04-03 00:06:02.000 (millis: 1112511962000)
>>> inserting: 2005-04-03 02:29:43.000 (millis: 1112520583000)
>>> inserting: 2005-04-03 03:02:09.000 (millis: 1112522529000)
>>> [UID]:16 [TrxTime]:2005-04-03 00:06:02.000 (millis: 1112511962000)
>>> [UID]:17 [TrxTime]:2005-04-03 03:29:43.000 (millis: 1112524183000)
>>> [UID]:18 [TrxTime]:2005-04-03 04:02:09.000 (millis: 1112526129000)
>>>
>>> So I write one thing and get something different back out. That
>>> doesn't seem correct. Surely there is a way to tell Postgres "to
>>> mess
>>> with my data" when you insert it?
>>>
>>> Christian
>>>
>>>
>>>
>>> On 7/15/05, Christian Cryder <c.s.cryder@gmail.com> wrote:
>>>
>>>> Ok, I think I've got a piece of code that dupes my problem:
>>>>
>>>> Here's how I'm creating my table (doesn't seem to matter whether
>>>> I use
>>>> 'with time zone' or not)...
>>>> CREATE TABLE Foo (
>>>>     UID            SERIAL,
>>>>     TrxTime        timestamp without time zone NOT NULL
>>>>     , PRIMARY KEY (UID)
>>>> );
>>>>
>>>> And here's the code that illustrates the problem...
>>>>     //change our timezone so that we are not operating in DST
>>>> (this allows us to
>>>>     //get un-munged timestamp values from src db)
>>>>     TimeZone curTz = TimeZone.getDefault();
>>>>     TimeZone.setDefault(new SimpleTimeZone(curTz.getRawOffset(),
>>>> curTz.getID()));
>>>>     System.out.println("current tz:"+TimeZone.getDefault());
>>>>
>>>>     //now we're going to write some sample data
>>>>     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd
>>>> HH:mm:ss.SSS");
>>>>     ObjectRepository or = ObjectRepository.getGlobalRepository();
>>>>     DataSource ds = (DataSource) or.getState
>>>> (AppKeys.DB_SYNC_TARGET);
>>>>     Connection conn = null;
>>>>     Statement stmt = null;
>>>>     PreparedStatement pstmt = null;
>>>>     Timestamp t = null;
>>>>     try {
>>>>         conn = ds.getConnection();
>>>>         stmt = conn.createStatement();
>>>>
>>>>         //clean up the table
>>>>         stmt.execute("DELETE FROM Foo");
>>>>
>>>>         //insert some sample data
>>>>         pstmt = conn.prepareStatement("INSERT INTO Foo (TrxTime)
>>>> VALUES (?)");
>>>>         t = new Timestamp(1112511962000L);        //2005-04-03
>>>> 00:06:02
>>>>         System.out.println("inserting: "+sdf.format(t));
>>>>         pstmt.setObject(1, t);
>>>>         pstmt.executeUpdate();
>>>>         t = new Timestamp(1112520583000L);        //2005-04-03
>>>> 02:29:43
>>>>         System.out.println("inserting: "+sdf.format(t));
>>>>         pstmt.setObject(1, t);
>>>>         pstmt.executeUpdate();
>>>>         t = new Timestamp(1112522529000L);        //2005-04-03
>>>> 03:02:09
>>>>         System.out.println("inserting: "+sdf.format(t));
>>>>         pstmt.setObject(1, t);
>>>>         pstmt.executeUpdate();
>>>>         if (!conn.getAutoCommit()) conn.commit();
>>>>
>>>>         //now read the values back out
>>>>         ResultSet rs = stmt.executeQuery("SELECT * FROM Foo");
>>>>         while (rs.next()) {
>>>>             System.out.println("[UID]:"+rs.getObject(1)+"
>>>> [TrxTime]:"+rs.getObject(2));
>>>>         }
>>>>         rs.close();
>>>>     } catch (SQLException e) {
>>>>         System.out.println("Unexpected SQLException: "+e);
>>>>         e.printStackTrace();
>>>>
>>>>     } finally {
>>>>         if (stmt!=null) try {stmt.close();} catch (SQLException
>>>> e) {}
>>>>         if (pstmt!=null) try {pstmt.close();} catch
>>>> (SQLException e) {}
>>>>         if (conn!=null) try {conn.close();} catch (SQLException
>>>> e) {}
>>>>     }
>>>>
>>>> Note that I am running in MST as my default system setting. I
>>>> modify
>>>> this at runtime so that I am NOT using daylight savings. Here's
>>>> what I
>>>> get for output:
>>>>
>>>> inserting: 2005-04-03 00:06:02.000
>>>> inserting: 2005-04-03 02:29:43.000
>>>> inserting: 2005-04-03 03:02:09.000
>>>>
>>>> [UID]:7 [TrxTime]:2005-04-03 00:06:02.0
>>>> [UID]:8 [TrxTime]:2005-04-03 03:29:43.0
>>>> [UID]:9 [TrxTime]:2005-04-03 04:02:09.0
>>>>
>>>> See how the data is getting changed when its written into the DB
>>>> (the
>>>> last 2 timestamps are bumped by an hour). Manually querying the DB
>>>> confirms that it got written in wrong
>>>>
>>>> What appears to be happening is that either the JDBC driver or
>>>> Postgres itself is munging the data on the way in, saying - "since
>>>> Postgres is running in MST w/ DST, I'd better adjust these
>>>> times". And
>>>> that's what I'm trying to avoid - I want it to write exactly what I
>>>> put in, with no adjustments.
>>>>
>>>> Any suggestions?
>>>>
>>>> tia,
>>>> Christian
>>>>
>>>>
>>>>
>>>> On 7/15/05, Dave Cramer <pg@fastcrypt.com> wrote:
>>>>
>>>>> Christian,
>>>>>
>>>>> Can you send me a snippet of code that shows me what you are
>>>>> trying
>>>>> to do ?
>>>>>
>>>>> Dave
>>>>> On 14-Jul-05, at 3:25 PM, Christian Cryder wrote:
>>>>>
>>>>>
>>>>>> On 7/14/05, Dave Cramer <pg@fastcrypt.com> wrote:
>>>>>>
>>>>>>
>>>>>>> Yeah, create your timestamps without timezones and they will
>>>>>>> not be
>>>>>>> converted.
>>>>>>>
>>>>>>>
>>>>>>
>>>>>> Dave, how exactly do you do this? Especially if I am trying to
>>>>>> read a
>>>>>> date out of the db (there is no timezone info there, but by
>>>>>> the time I
>>>>>> access the data via ps.getDate() its already there).
>>>>>>
>>>>>> Any suggestions would be greatly appreciated.
>>>>>>
>>>>>> Thanks,
>>>>>> Christian
>>>>>>
>>>>>> ---------------------------(end of
>>>>>> broadcast)---------------------------
>>>>>> TIP 6: explain analyze is your friend
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>