Re: Timezone conversion woes

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
> > > > >
> > > > >
> > > >
> > > >
> > >
> >
>