Thread: Timezone conversion woes

Ok, let me see if I can explain this simply. Is there any way to
read/write JDBC date/time/timestamp data through the postgres drivers
WITHOUT having any timezone conversion issues?

I've searched high and low for info on this and not found any
conclusive answers.

What is driving this problem is that we are reading data out of one DB
(MS SQL, using Net Direct drivers), and writing it back into Postgres
8.

In most cases, the NetDirect drivers leave timezone info alone, EXCEPT
when the date in question happens to fall into a daylight savings time
issue. In other words, when reading a "zoneless" time out of the db,
NetDirect says "hey, that's not a valid time, because it falls into
daylight savings time "no mans land" (between 2-3 AM, 1st Sunday of
April), and so it rolls it forward to what it considers a valid time.
The only way we have found to offset this is to tell the JVM we are
running in UTC. Then NetDirect doesn't do the conversion.

However, as soon as we do this, we now have a problem on the Postgres
side - Postgres says "oh, you're running in UTC, but the DB is running
as MST, so I better convert that date for you." Argh. No, that is not
what we want. We can compensate by telling Postgres to run in UTC, but
that's not really what we want to do either (ie. because then, any
code that writes data into the DB has to remember to set its jvm
timezone to UTC as well).

What we really want to do here is just tell the drivers - "leave my
dates alone, pal!" Is there any way to do that? I realize that the
NetDirect behavior is kind of at the root of this, but we haven't
found any way to change that. So please don't just say - "your
screwed". Our goal is to sucessfully migrate to Postgres, here, so I'd
really appreciate solutions rather than finger pointing.

Any suggestions?

Thanks,
Christian

Hi Reid,

I am having a hard time locating this section (6.8.3) in the
documentation (I'm looking both here:
http://www.postgresql.org/docs/8.0/interactive/index.html and here:
http://jdbc.postgresql.org/documentation/80/index.html and not seeing
it in either place.) Where should I be looking?

Regarding the AT TIME ZONE option, it looks like that is specific to
Postgres, and works with the select, yes? My problem here is that I am
reading from MS SQL and trying to _write_ to Postgres. So I'm not sure
how this would help me.

Can anyone tell me if its possible to read a Date/Time/Timestamp as a
"bytes" value and then write it back that way somehow? Any other
suggestions?

THanks much,
Christian

On 7/14/05, Reid Thompson <Reid.Thompson@ateb.com> wrote:
> Christian Cryder wrote:
> > Ok, let me see if I can explain this simply. Is there any way
> > to read/write JDBC date/time/timestamp data through the
> > postgres drivers WITHOUT having any timezone conversion issues?
> >
> > I've searched high and low for info on this and not found any
> > conclusive answers.
> >
> > What is driving this problem is that we are reading data out
> > of one DB (MS SQL, using Net Direct drivers), and writing it back
> > into Postgres 8.
> >
> > In most cases, the NetDirect drivers leave timezone info
> > alone, EXCEPT when the date in question happens to fall into
> > a daylight savings time issue. In other words, when reading a
> > "zoneless" time out of the db, NetDirect says "hey, that's
> > not a valid time, because it falls into daylight savings time
> > "no mans land" (between 2-3 AM, 1st Sunday of April), and so
> > it rolls it forward to what it considers a valid time. The
> > only way we have found to offset this is to tell the JVM we
> > are running in UTC. Then NetDirect doesn't do the conversion.
> >
> > However, as soon as we do this, we now have a problem on the
> > Postgres side - Postgres says "oh, you're running in UTC, but
> > the DB is running as MST, so I better convert that date for
> > you." Argh. No, that is not what we want. We can compensate
> > by telling Postgres to run in UTC, but that's not really what
> > we want to do either (ie. because then, any code that writes
> > data into the DB has to remember to set its jvm timezone to UTC as
> > well).
> >
> > What we really want to do here is just tell the drivers -
> > "leave my dates alone, pal!" Is there any way to do that? I
> > realize that the NetDirect behavior is kind of at the root of
> > this, but we haven't found any way to change that. So please
> > don't just say - "your screwed". Our goal is to sucessfully
> > migrate to Postgres, here, so I'd really appreciate solutions rather
> > than finger pointing.
> >
> > Any suggestions?
> >
> > Thanks,
> > Christian
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
>                http://www.postgresql.org/docs/faq
>
> would this be of use
>
> 6.8.3. AT TIME ZONE
>
> The AT TIME ZONE construct allows conversions of timestamps to different
> timezones.
>
> Table 6-19. AT TIME ZONE Variants
> Expression      Returns         Description
> timestamp without time zone AT TIME ZONE zone   timestamp with time zone
> Convert local time in given timezone to UTC
> timestamp with time zone AT TIME ZONE zone      timestamp without time
> zone    Convert UTC to local time in given timezone
> time with time zone AT TIME ZONE zone   time with time zone     Convert
> local time across timezones
>
> In these expressions, the desired time zone can be specified either as a
> text string (e.g., 'PST') or as an interval (e.g., INTERVAL '-08:00').
>
> Examples (supposing that TimeZone is PST8PDT):
>
> SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
>  Result: 2001-02-16 19:38:40-08
>
>  SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE
> 'MST';
>  Result: 2001-02-16 18:38:40
>
> The first example takes a zone-less timestamp and interprets it as MST
> time (GMT-7) to produce a UTC timestamp, which is then rotated to PST
> (GMT-8) for display. The second example takes a timestamp specified in
> EST (GMT-5) and converts it to local time in MST (GMT-7).
>
> The function timezone(zone, timestamp) is equivalent to the
> SQL-compliant construct timestamp AT TIME ZONE zone.
>
> reid
>

Yeah, create your timestamps without timezones and they will not be
converted.

Dave
On 14-Jul-05, at 2:22 PM, Christian Cryder wrote:

> Hi Reid,
>
> I am having a hard time locating this section (6.8.3) in the
> documentation (I'm looking both here:
> http://www.postgresql.org/docs/8.0/interactive/index.html and here:
> http://jdbc.postgresql.org/documentation/80/index.html and not seeing
> it in either place.) Where should I be looking?
>
> Regarding the AT TIME ZONE option, it looks like that is specific to
> Postgres, and works with the select, yes? My problem here is that I am
> reading from MS SQL and trying to _write_ to Postgres. So I'm not sure
> how this would help me.
>
> Can anyone tell me if its possible to read a Date/Time/Timestamp as a
> "bytes" value and then write it back that way somehow? Any other
> suggestions?
>
> THanks much,
> Christian
>
> On 7/14/05, Reid Thompson <Reid.Thompson@ateb.com> wrote:
>
>> Christian Cryder wrote:
>>
>>> Ok, let me see if I can explain this simply. Is there any way
>>> to read/write JDBC date/time/timestamp data through the
>>> postgres drivers WITHOUT having any timezone conversion issues?
>>>
>>> I've searched high and low for info on this and not found any
>>> conclusive answers.
>>>
>>> What is driving this problem is that we are reading data out
>>> of one DB (MS SQL, using Net Direct drivers), and writing it back
>>> into Postgres 8.
>>>
>>> In most cases, the NetDirect drivers leave timezone info
>>> alone, EXCEPT when the date in question happens to fall into
>>> a daylight savings time issue. In other words, when reading a
>>> "zoneless" time out of the db, NetDirect says "hey, that's
>>> not a valid time, because it falls into daylight savings time
>>> "no mans land" (between 2-3 AM, 1st Sunday of April), and so
>>> it rolls it forward to what it considers a valid time. The
>>> only way we have found to offset this is to tell the JVM we
>>> are running in UTC. Then NetDirect doesn't do the conversion.
>>>
>>> However, as soon as we do this, we now have a problem on the
>>> Postgres side - Postgres says "oh, you're running in UTC, but
>>> the DB is running as MST, so I better convert that date for
>>> you." Argh. No, that is not what we want. We can compensate
>>> by telling Postgres to run in UTC, but that's not really what
>>> we want to do either (ie. because then, any code that writes
>>> data into the DB has to remember to set its jvm timezone to UTC as
>>> well).
>>>
>>> What we really want to do here is just tell the drivers -
>>> "leave my dates alone, pal!" Is there any way to do that? I
>>> realize that the NetDirect behavior is kind of at the root of
>>> this, but we haven't found any way to change that. So please
>>> don't just say - "your screwed". Our goal is to sucessfully
>>> migrate to Postgres, here, so I'd really appreciate solutions rather
>>> than finger pointing.
>>>
>>> Any suggestions?
>>>
>>> Thanks,
>>> Christian
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 3: Have you checked our extensive FAQ?
>>>
>>>
>>                http://www.postgresql.org/docs/faq
>>
>> would this be of use
>>
>> 6.8.3. AT TIME ZONE
>>
>> The AT TIME ZONE construct allows conversions of timestamps to
>> different
>> timezones.
>>
>> Table 6-19. AT TIME ZONE Variants
>> Expression      Returns         Description
>> timestamp without time zone AT TIME ZONE zone   timestamp with
>> time zone
>> Convert local time in given timezone to UTC
>> timestamp with time zone AT TIME ZONE zone      timestamp without
>> time
>> zone    Convert UTC to local time in given timezone
>> time with time zone AT TIME ZONE zone   time with time zone
>> Convert
>> local time across timezones
>>
>> In these expressions, the desired time zone can be specified
>> either as a
>> text string (e.g., 'PST') or as an interval (e.g., INTERVAL
>> '-08:00').
>>
>> Examples (supposing that TimeZone is PST8PDT):
>>
>> SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
>>  Result: 2001-02-16 19:38:40-08
>>
>>  SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME
>> ZONE
>> 'MST';
>>  Result: 2001-02-16 18:38:40
>>
>> The first example takes a zone-less timestamp and interprets it as
>> MST
>> time (GMT-7) to produce a UTC timestamp, which is then rotated to PST
>> (GMT-8) for display. The second example takes a timestamp
>> specified in
>> EST (GMT-5) and converts it to local time in MST (GMT-7).
>>
>> The function timezone(zone, timestamp) is equivalent to the
>> SQL-compliant construct timestamp AT TIME ZONE zone.
>>
>> reid
>>
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

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

When you create the column in the database

do create table foo( t timestamp without time zone )

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

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

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

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

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

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

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