Re: Timestamp Conversion Woes Redux

Christian,


Try adding protocolVersion=2 to your url and then it will not use
bind, and you will
get logging messages in postgresql logs.

Also what is the timezone setting for your server ?

Dave
On 18-Jul-05, at 1:50 PM, Christian Cryder wrote:

> Hi Dave (& others),
>
> You said,
>
>> The problem isn't with PreparedStatement, rather with Timestamp
>> itself.
>> I ran the following
>>     Timestamp ts = new Timestamp(1112511962000L);
>>     System.out.println(ts);
>> it prints out
>>     2005-04-03 03:06:02.0
>>
>
> Actually, I still think it is a problem w/ PreparedStatement, and I'll
> see if I can explain why, as well as provide a better test case to
> illustrate.
>
> First, the reason you got what you did up above is because you are
> running in a different timezone than I am. If you were to run your
> example where I live (MST), you'd get this:
>
>     2005-04-03 00:06:02.0 (MST)
>     2005-04-03 00:06:02.0 (MST - w/ daylight savings turned off)
>
> See how those values are both the same? Now watch what happens if we
> were to run the exact same piece of code in EST:
>
>     2005-04-03 03:06:02.0 (EST)
>     2005-04-03 02:06:02.0 (EST - w/ daylight savings turned off)
>
> See how they are different? What is happening here is that 2:06 is the
> actual time (EST is 2 hrs before MST, right?). But on April 3, 2005,
> 2:06 is not a valid time - because that is right in between when the
> time is supposed to be springing forward for daylight savings.
>
> With me so far? My point here is just that the single millisecond
> value we are looking at (1112511962000L) can actually be represented
> as two different things, depending on your relation to the timezone.
>
> So let's say I read 2005-04-03 02:06:02.0 in from a db - that
> timestamp will convert to the millisecond value given above, but then
> when we write it back out its going to get rolled forward an hour
> because of daylight savings. See the problem? The write just applied
> timezone rules to my data and modified it whether I wanted it to or
> not.
>
> Now, this is only happening with prepared statements, not w/ regular
> statements, and the attached example (below) illustrates that clearly.
>
> Let's switch back to my original example, in MST. There were 3
> timestamp strings:
>     t1: 2005-04-03 00:06:02.000 - before the DST cutoff
>     t2: 2005-04-03 02:29:43.000 - during the DST rollover
>     t3: 2005-04-03 03:02:09.000 - after the DST rollover
>
> The point here is that if we are applying timezone rules and DST is
> turned on, that second value t2 is actually not valid - it's right in
> the middle of the switch. So a timestamp would format it as 03:29:43.
> Which is fine - except for the fact that I am reading the date from a
> "timestamp without timezone" column.
>
> Now, if I run my example code (see below) with usepstmt = false, the
> code uses regular statements and I get the following results:
>
> (usepstmt = false)
> ------------------
> current tz: java.util.SimpleTimeZone
> [id=MST,offset=-25200000,dstSavings=3600000,
>     useDaylight=false,...<snipped>]
>
> starting t1: 2005-04-03 00:06:02.000
> starting t2: 2005-04-03 02:29:43.000
> starting t3: 2005-04-03 03:02:09.000
>
> inserting t1: 2005-04-03 00:06:02.0 (millis: 1112511962000)
> inserting t2: 2005-04-03 02:29:43.0 (millis: 1112520583000)
> inserting t3: 2005-04-03 03:02:09.0 (millis: 1112522529000)
>
> resulting t1: [UID]:112 [TrxTime]:2005-04-03 00:06:02.0 (millis:
> 1112511962000)
> resulting t2: [UID]:113 [TrxTime]:2005-04-03 02:29:43.0 (millis:
> 1112520583000)
> resulting t3: [UID]:114 [TrxTime]:2005-04-03 03:02:09.0 (millis:
> 1112522529000)
>
> See how all 3 of the values stay the same? JDBC inserted all 3 dates
> exactly as we requested. Now, watch what happens when I use prepared
> statements instead:
>
> (usepstmt = true)
> ------------------
> current tz: java.util.SimpleTimeZone
> [id=MST,offset=-25200000,dstSavings=3600000,
>     useDaylight=false,...<snipped>]
>
> starting t1: 2005-04-03 00:06:02.000
> starting t2: 2005-04-03 02:29:43.000
> starting t3: 2005-04-03 03:02:09.000
>
> inserting t1: 2005-04-03 00:06:02.0 (millis: 1112511962000)
> inserting t2: 2005-04-03 02:29:43.0 (millis: 1112520583000)
> inserting t3: 2005-04-03 03:02:09.0 (millis: 1112522529000)
>
> resulting t1: [UID]:115 [TrxTime]:2005-04-03 00:06:02.0 (millis:
> 1112511962000)
> resulting t2: [UID]:116 [TrxTime]:2005-04-03 03:29:43.0 (millis:
> 1112524183000)
> resulting t3: [UID]:117 [TrxTime]:2005-04-03 04:02:09.0 (millis:
> 1112526129000)
>
> See what happened? t2 and t3 got bumped forward an hour in the db. So
> even though we said "insert 02:29:43" something in the prepared
> statement applied some kind of daylight savings logic and rolled those
> last two dates forward.
>
> And THAT is where the problem lies. PreparedStatement needs to handle
> these dates the same way that regular Statement does.
>
> Does that help explain the issue any better? Please holler if you need
> any further information. I am proceeding to try and poke around in the
> jdbc source, but I'm not really sure where I should be looking.
>
> This is a HUGE issue for us as we are trying to switch from MS SQL to
> Postgres - we have to find a way to fix this issue asap. So if anyone
> has any ideas, I am all ears.
>
> Thanks!
> Christian
> -------------------------------------------
> Sample Table...
> -------------------------------------------
>
> CREATE TABLE Foo (
>    UID            SERIAL,
>    TrxTime        timestamp without time zone NOT NULL
>    , PRIMARY KEY (UID)
> );
>
> -------------------------------------------
> Sample Code...
> -------------------------------------------
>     //set the timezone to MST so that others can easily replicate,
> then
>     //install a variant where daylight savings is turned off (this
> will allow us to
>     //see the source dates un-munged, which is important here)
>     TimeZone.setDefault(TimeZone.getTimeZone("MST"));
>     TimeZone curTz = TimeZone.getDefault();
>     TimeZone.setDefault(new SimpleTimeZone(curTz.getRawOffset(),
> curTz.getID()));   //if you don't do this, t1 and t2 will get rolled
> forward because of DST...
>     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");
>     System.out.println("starting t1: "+sdf.format(new
> Timestamp(1112511962000L)));     //2005-04-03 00:06:02
>     System.out.println("starting t2: "+sdf.format(new
> Timestamp(1112520583000L)));     //2005-04-03 02:29:43
>     System.out.println("starting t3: "+sdf.format(new
> Timestamp(1112522529000L)));     //2005-04-03 03:02:09
>
>     //here we go...
>     Connection conn = null;
>     Statement stmt = null;
>     PreparedStatement pstmt = null;
>     Timestamp t = null;
>     Calendar cal = Calendar.getInstance();
>     boolean usepstmt = true;
>     try {
>         conn = ds.getConnection();
>         stmt = conn.createStatement();
>         pstmt = conn.prepareStatement("INSERT INTO Foo (TrxTime)
> VALUES (?)");
>
>         //clean up the table
>         stmt.execute("DELETE FROM Foo");
>
>         //insert some sample data
>         //...2005-04-03 00:06:02 (before the DST cutover)
>         t = new Timestamp(1112511962000L);
>         System.out.println("inserting t1: "+t+" (millis: "+t.getTime
> ()+")");
>         if (usepstmt) {
>             pstmt.setTimestamp(1, t);
>             pstmt.executeUpdate();
>         } else {
>             stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
>         }
>
>         //...2005-04-03 02:29:43 (during the DST cutover)
>         t = new Timestamp(1112520583000L);
>         System.out.println("inserting t2: "+t+" (millis: "+t.getTime
> ()+")");
>         if (usepstmt) {
>             pstmt.setTimestamp(1, t);
>             pstmt.executeUpdate();
>         } else {
>             stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
>         }
>
>         //...2005-04-03 03:02:09 (after the DST cutover)
>         t = new Timestamp(1112522529000L);
>         System.out.println("inserting t3: "+t+" (millis: "+t.getTime
> ()+")");
>         if (usepstmt) {
>             pstmt.setTimestamp(1, t);
>             pstmt.executeUpdate();
>         } else {
>             stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
> ('"+sdf.format(t)+"')");
>         }
>
>         if (!conn.getAutoCommit()) conn.commit();
>
>         //now read the values back out
>         ResultSet rs = stmt.executeQuery("SELECT * FROM Foo");
>         int cntr = 0;
>         while (rs.next()) {
>             t = rs.getTimestamp(2);
>             System.out.println("resulting t"+(++cntr)+":
> [UID]:"+rs.getObject(1)+" [TrxTime]:"+t+" (millis: "+t.getTime()+")");
>         }
>         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) {}
>     }
>
>
> :
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>