Re: Timestamp Conversion Woes Redux - Mailing list pgsql-jdbc
From | Dave Cramer |
---|---|
Subject | Re: Timestamp Conversion Woes Redux |
Date | |
Msg-id | 1F6C7878-75C4-4089-943A-3CFA03E2194C@fastcrypt.com Whole thread Raw |
In response to | Timestamp Conversion Woes Redux (Christian Cryder <c.s.cryder@gmail.com>) |
List | pgsql-jdbc |
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 > >
pgsql-jdbc by date: