Re: Timezone conversion woes

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