Re: Timezone conversion woes - Mailing list pgsql-jdbc

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