Обсуждение: Timestamp Conversion Woes Redux
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) {} } :
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 > >
Christian Cryder <c.s.cryder@gmail.com> writes: >> The problem isn't with PreparedStatement, rather with Timestamp itself. > 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. I'm hardly a JDBC expert, but I recall some considerable discussion awhile back about how JDBC ought to map Java's (one) Timestamp type into Postgres' TIMESTAMP WITH/WITHOUT TIME ZONE types, neither of which apparently match the semantics of Timestamp very well. You should go digging in the pgsql-jdbc archives for background. As for the problem at hand, I suspect that the driver is prespecifying the parameter data type as either TIMESTAMP WITH or TIMESTAMP WITHOUT TIME ZONE, and that whichever choice is used is different from what the server would infer without the prespecification. This would in turn affect what the server assumes about the timezone spec (or lack of one) in the supplied input string. Worse, there could be an ensuing run-time conversion between the two data types, leading to adding or subtracting your local GMT offset. (For that matter, is the parameter being sent in text or binary? If it's binary then most of these theories fall to the ground...) regards, tom lane
Tom, You are correct, the driver is binding it to a timestamptz. The underlying data is without timezone so the server probably attempts to remove it I'm starting to think that binding the type to "unknown" might be better as you suggested earlier. Dave On 18-Jul-05, at 3:26 PM, Tom Lane wrote: > Christian Cryder <c.s.cryder@gmail.com> writes: > >>> The problem isn't with PreparedStatement, rather with Timestamp >>> itself. >>> > > >> 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. >> > > I'm hardly a JDBC expert, but I recall some considerable discussion > awhile back about how JDBC ought to map Java's (one) Timestamp type > into Postgres' TIMESTAMP WITH/WITHOUT TIME ZONE types, neither of > which apparently match the semantics of Timestamp very well. You > should go digging in the pgsql-jdbc archives for background. > > As for the problem at hand, I suspect that the driver is prespecifying > the parameter data type as either TIMESTAMP WITH or TIMESTAMP WITHOUT > TIME ZONE, and that whichever choice is used is different from what > the server would infer without the prespecification. This would in > turn affect what the server assumes about the timezone spec (or lack > of one) in the supplied input string. Worse, there could be an > ensuing run-time conversion between the two data types, leading to > adding or subtracting your local GMT offset. > > (For that matter, is the parameter being sent in text or binary? > If it's binary then most of these theories fall to the ground...) > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >
Tom Lane wrote: > (For that matter, is the parameter being sent in text or binary? > If it's binary then most of these theories fall to the ground...) Text -- the only type the driver currently uses binary parameters for is bytea, from memory. -O
Dave Cramer wrote: > You are correct, the driver is binding it to a timestamptz. The > underlying data is without > timezone so the server probably attempts to remove it > > I'm starting to think that binding the type to "unknown" might be > better as you suggested earlier. Yeah, given that we can't work out which of the two types is intended ahead of time, this might be the best idea.. Christian, you should be able to test this easily enough by modifying setTimestamp() to pass Oid.UNKNOWN instead of Oid.TIMESTAMPTZ to bindString(). -O
I'm also thinking we should use UNKOWN for setString as well, hopefully this would reduce the number of upgrade problems people are having when they upgrade from 7.x to 8.x Dave On 18-Jul-05, at 7:13 PM, Oliver Jowett wrote: > Dave Cramer wrote: > > >> You are correct, the driver is binding it to a timestamptz. The >> underlying data is without >> timezone so the server probably attempts to remove it >> >> I'm starting to think that binding the type to "unknown" might be >> better as you suggested earlier. >> > > Yeah, given that we can't work out which of the two types is intended > ahead of time, this might be the best idea.. > > Christian, you should be able to test this easily enough by modifying > setTimestamp() to pass Oid.UNKNOWN instead of Oid.TIMESTAMPTZ to > bindString(). > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > >
Dave Cramer wrote: > I'm also thinking we should use UNKOWN for setString as well, hopefully > this would reduce the number of upgrade problems people are having when > they upgrade from 7.x to 8.x I still think this is a bad idea. -O
Oliver, Can you explain your rationale ? Mine is: The overhead isn't that bad. Anyone using the API correctly will not be affected ( assuming they use setInt() etal correctly ) Philosophically speaking, making the driver more strict does not make it easier for people to convert to postgresql. AFAICT, most drivers tend to be more lenient. Dave On 19-Jul-05, at 8:23 AM, Oliver Jowett wrote: > Dave Cramer wrote: > >> I'm also thinking we should use UNKOWN for setString as well, >> hopefully this would reduce the number of upgrade problems people >> are having when they upgrade from 7.x to 8.x >> > > I still think this is a bad idea. > > -O > >
Dave Cramer wrote: > Can you explain your rationale ? I don't really want to rehash this again and again, but it boils down to: if setString() does not type the parameter as a string, how *do* I type a parameter as a string? I do not find "there are lots of broken applications out there" a particularly convincing argument for changing this. -O
Oliver, Sorry for bringing this up again. I realize we have discussed this before. by typing it as UNKNOWN we are letting the backend decide what to do with it. There is considerable code in there to do the "right" thing. Dave On 19-Jul-05, at 8:45 AM, Oliver Jowett wrote: > Dave Cramer wrote: > >> Can you explain your rationale ? >> > > I don't really want to rehash this again and again, but it boils > down to: if setString() does not type the parameter as a string, > how *do* I type a parameter as a string? > > I do not find "there are lots of broken applications out there" a > particularly convincing argument for changing this. > > -O > >
Dave Cramer wrote: > by typing it as UNKNOWN we are letting the backend decide what to do > with it. There is considerable code in there to do the "right" thing. So you are suggesting we type all parameters as UNKNOWN regardless of which setXXX() accessor was used to set them? If not, why is setString() special? -O
Oliver, setString is "special" because historically this is what we see in the upgrade problems. People try to insert a date using setString ('dateval') etc. Also we have historically told people to use setString to insert pg specific types, when there was no corresponding java type. I am not suggesting that we type all parameters as UNKNOWN only String parameters. Dave On 19-Jul-05, at 9:07 AM, Oliver Jowett wrote: > Dave Cramer wrote: > > >> by typing it as UNKNOWN we are letting the backend decide what to >> do with it. There is considerable code in there to do the "right" >> thing. >> > > So you are suggesting we type all parameters as UNKNOWN regardless > of which setXXX() accessor was used to set them? > > If not, why is setString() special? > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >
Dave Cramer wrote: > setString is "special" because historically this is what we see in the > upgrade problems. People try to insert a date using setString > ('dateval') etc. And we're back to: I think the right solution here is for people to fix their broken apps, not to introduce unpredictable behaviour in the driver. > Also we have historically told people to use setString to insert pg specific types, when there was no corresponding javatype. That's a fair point. We really need a proper way for specifying this anyway; I had a trivial PGobject implementation at one point that did the trick, but Kris didn't like the other bits I did in that patch and I never got around to implementing it separately. -O
Oliver Jowett <oliver@opencloud.com> writes: > Dave Cramer wrote: >> I'm also thinking we should use UNKOWN for setString as well, hopefully >> this would reduce the number of upgrade problems people are having when >> they upgrade from 7.x to 8.x > I still think this is a bad idea. I think one main point against using UNKNOWN is that it creates a risk of "could not resolve parameter type" query failures. That's OK for generic setString() cases, since the user can always escape the failure by changing his code to specify the parameter type more clearly. The other argument against UNKNOWN is that the backend might choose an unexpected data type. Again, that doesn't scare me a lot for setString, because the backend's rules for dealing with UNKNOWN are biased in favor of resolving the parameter type as TEXT, which seems perfectly reasonable for setString cases. Unfortunately, both of these considerations speak *against* using UNKNOWN for Timestamp. If the backend rejects the query --- or more likely, makes the wrong datatype choice --- there will be no way for the user to fix it. So I'm in favor of using UNKNOWN for setString, but I think we gotta find another answer for Christian's problem. regards, tom lane
Tom Lane wrote: > I think one main point against using UNKNOWN is that it creates a risk > of "could not resolve parameter type" query failures. That's OK for > generic setString() cases, since the user can always escape the failure > by changing his code to specify the parameter type more clearly. > The other argument against UNKNOWN is that the backend might choose an > unexpected data type. Again, that doesn't scare me a lot for setString, > because the backend's rules for dealing with UNKNOWN are biased in favor > of resolving the parameter type as TEXT, which seems perfectly > reasonable for setString cases. The main thing I'm worried about there is that if there are cases where an UNKNOWN parameter will generate an error rather than resolve to TEXT, then the driver has just backed the user into a corner they can't escape from. Are there any cases where this can happen? (I'm thinking of some of the ambiguous-type problems we ran into when sending nulls as UNKNOWN..) -O
Oliver Jowett <oliver@opencloud.com> writes: > The main thing I'm worried about there is that if there are cases where > an UNKNOWN parameter will generate an error rather than resolve to TEXT, > then the driver has just backed the user into a corner they can't escape > from. Are there any cases where this can happen? There are some, for instance someone was just complaining about this: template1=# select 1 where 5 in (select ''); ERROR: failed to find conversion function from "unknown" to integer My previous response was based on what was in my inbox, which I now see wasn't the whole thread. I agree with you that if we make setString default to UNKNOWN, there had better be a way to say "by golly this really is TEXT" for the corner cases. It'd be a good idea if it wasn't limited to TEXT, either, but could allow specification of any random datatype. I believe that UNKNOWN will work fine for 99% of cases out there, because the backend's algorithms have been tuned for years to generally do the right thing when presented with unadorned literal strings --- but there has to be an escape hatch for the other 1%. Of course, there's always the escape hatch of changing the query text, that is write select ... cast(? as text) or select ... ?::text but this seems pretty crude. regards, tom lane
Hi all, My opinion as a user who just "fixed" his application to properly set the data base types: fixing the application is a bigger PITA than fixing weird cases of strange type choices of the server. The first one involves changing lots of exiting code, the second involves newly written code, as I think the old code relying on setting string and letting the server decide what is it should work fine. In my case I've had to make extra meta data look-ups to be able to properly set the data type in some of our more generic code, which is adding some complexity overhead to my code (I presume the performance difference is negligible). Given the fact that the server is very likely to decide correctly the type, and a lot of code can be simpler if no exact knowledge of the data types is needed, I think setting the type to unknown should be acceptable. Actually, will this allow to use setString on a boolean field using preaparedStatements ? 'cause that's the one which gave me the headaches. Just my 2c. Cheers, Csaba. On Tue, 2005-07-19 at 16:03, Oliver Jowett wrote: > Tom Lane wrote: > > > I think one main point against using UNKNOWN is that it creates a risk > > of "could not resolve parameter type" query failures. That's OK for > > generic setString() cases, since the user can always escape the failure > > by changing his code to specify the parameter type more clearly. > > > The other argument against UNKNOWN is that the backend might choose an > > unexpected data type. Again, that doesn't scare me a lot for setString, > > because the backend's rules for dealing with UNKNOWN are biased in favor > > of resolving the parameter type as TEXT, which seems perfectly > > reasonable for setString cases. > > The main thing I'm worried about there is that if there are cases where > an UNKNOWN parameter will generate an error rather than resolve to TEXT, > then the driver has just backed the user into a corner they can't escape > from. Are there any cases where this can happen? (I'm thinking of some > of the ambiguous-type problems we ran into when sending nulls as UNKNOWN..) > > -O > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
[snip] > wasn't the whole thread. I agree with you that if we make setString > default to UNKNOWN, there had better be a way to say "by golly this > really is TEXT" for the corner cases. It'd be a good idea if it wasn't > limited to TEXT, either, but could allow specification of any random > datatype. This makes me think, isn't it possible to introduce a special type to say something like: setObject(..., Types.UNKNOWN), and map that to setting a string with type unknown ? In that case people could still use prepared statements with parameters of unknown type, it just have to be explicit. For me that would have been a much simpler "fixing the app". Cheers, Csaba.
What about creating two extension classes PGTimestamp, and PGTimestamptz then allowing setObject to recognize these internally and bind to Oid.Timestamp, and Oid.Timestamptz repectively for setString I am in favour of using UNKNOWN as this is no worse than what 7.4 drivers do now Dave On 19-Jul-05, at 9:54 AM, Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> writes: > >> Dave Cramer wrote: >> >>> I'm also thinking we should use UNKOWN for setString as well, >>> hopefully >>> this would reduce the number of upgrade problems people are >>> having when >>> they upgrade from 7.x to 8.x >>> > > >> I still think this is a bad idea. >> > > I think one main point against using UNKNOWN is that it creates a risk > of "could not resolve parameter type" query failures. That's OK for > generic setString() cases, since the user can always escape the > failure > by changing his code to specify the parameter type more clearly. > > The other argument against UNKNOWN is that the backend might choose an > unexpected data type. Again, that doesn't scare me a lot for > setString, > because the backend's rules for dealing with UNKNOWN are biased in > favor > of resolving the parameter type as TEXT, which seems perfectly > reasonable for setString cases. > > Unfortunately, both of these considerations speak *against* using > UNKNOWN for Timestamp. If the backend rejects the query --- or more > likely, makes the wrong datatype choice --- there will be no way for > the user to fix it. > > So I'm in favor of using UNKNOWN for setString, but I think we gotta > find another answer for Christian's problem. > > regards, tom lane > >
For that matter we could use a PGUnknown type as well. Dave. On 19-Jul-05, at 10:52 AM, Csaba Nagy wrote: > [snip] > >> wasn't the whole thread. I agree with you that if we make setString >> default to UNKNOWN, there had better be a way to say "by golly this >> really is TEXT" for the corner cases. It'd be a good idea if it >> wasn't >> limited to TEXT, either, but could allow specification of any random >> datatype. >> > > This makes me think, isn't it possible to introduce a special type to > say something like: setObject(..., Types.UNKNOWN), and map that to > setting a string with type unknown ? In that case people could > still use > prepared statements with parameters of unknown type, it just have > to be > explicit. For me that would have been a much simpler "fixing the app". > > Cheers, > Csaba. > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > >
Ok, that would be more type safe too. For my code it doesn't matter anymore though, I've already fixed it to set the right type... but it would have saved me about 2 days of work. Cheers, Csaba. On Tue, 2005-07-19 at 17:31, Dave Cramer wrote: > For that matter we could use a PGUnknown type as well. > > Dave. > On 19-Jul-05, at 10:52 AM, Csaba Nagy wrote: > > > [snip] > > > >> wasn't the whole thread. I agree with you that if we make setString > >> default to UNKNOWN, there had better be a way to say "by golly this > >> really is TEXT" for the corner cases. It'd be a good idea if it > >> wasn't > >> limited to TEXT, either, but could allow specification of any random > >> datatype. > >> > > > > This makes me think, isn't it possible to introduce a special type to > > say something like: setObject(..., Types.UNKNOWN), and map that to > > setting a string with type unknown ? In that case people could > > still use > > prepared statements with parameters of unknown type, it just have > > to be > > explicit. For me that would have been a much simpler "fixing the app". > > > > Cheers, > > Csaba. > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > >
On Tue, 19 Jul 2005, Dave Cramer wrote: > What about creating two extension classes > > PGTimestamp, and PGTimestamptz > > then allowing setObject to recognize these internally and bind to > Oid.Timestamp, and Oid.Timestamptz repectively > Who is going to really use these unportable things instead of standard Timestamps? People who are desperate to get their application to work around driver bugs? I don't believe timestamp/timestamptz is an intractable problem and in the previous discussion I recall suggesting setting the server time zone to match the client's would fix this problem. I also seem to recall a number of other timezone related issues. Let's focus on trying to fix what we've got before we go down this unportable route. I'll put together at test cases for the bugs I'm aware of tonight so we can focus this discussion on the real problems. Kris Jurka
Hi Kris, Dave and I have been talking about this a lot over the past couple of days, and I've provided some sample code that will clearly illustrate the problem. At the heart of the issue is the fact that there needs to be a way to insert Timestamps into the DB using PreparedStatements, where you can indicate to the DB "do not munge this date, thank you very much". Right now, if you are using Timestamps, that will happen regardless of whether you are declaring the timestamp with or without timezone. So to summarize: the sql standard has the notion of 2 kinds of timestamp (w/, w/out timezone). The JDBC API on the other hand doesn't - it's just "timestamp". And so the drivers have to make assumptions about how to send the data across. The current implementation (which uses TIMESTAMPTZ) _guarantees_ the server will remap the time. And that is very much a problem (as my sample code illustrates, especially when you are trying to store zoneless times). As an example: consider what happens when you try and insert a time like 2005-04-03 2:39:00. If daylight savings is turned on, this is not a valid time, because it falls between 2 and 3 AM on the first Sunday in April. So when you try and insert/update it into the db, it will "adjust" it for you. But if this is zoneless data, then it's perfectly legit. Unfortunately, there's no way via the JDBC PreparedStatement (as currently implemented) to keep that munging from happening. And that is very much a problem. I should not have to configure my server to turn daylight savings off in order to get stuff to insert into the DB correctly (after all, other things run on the same server). From my perspective, there needs to be a way for the JDBC driver to know what type of timestamp you are working with. That information resides in the DB, so you _could_ query the metadata info to get that info up front. The problem w/ that of course is that it introduces a performance penalty (unless you can come up w/ some kind of caching strategy). So the only other option is to pass in some kind of hint to the JDBC driver on the client side. I'm not wild about referencing Postgres specific types in our code, but if that's the only alternative, I can definitely live with it. I DON'T like the idea of having to shove timestamps in via setString(), because it seems hacky and counterintuitive (after all, they aren't Strings!). It still seems to me that the easiest solution would be to have setTimestamp() map with type UNKNOWN - the decision would get delegated to the server, based on the column type definition, and the only downside is that if someone tried to insert a timestamp into a String column, it would work without erring (which it would do anyway if we tweaked setString to use type UNKNOWN). So those are my thoughts. Please feel free to holler if anything isn't clear... > > Kris Jurka >
Csaba Nagy wrote: > [snip] > >>wasn't the whole thread. I agree with you that if we make setString >>default to UNKNOWN, there had better be a way to say "by golly this >>really is TEXT" for the corner cases. It'd be a good idea if it wasn't >>limited to TEXT, either, but could allow specification of any random >>datatype. > > > This makes me think, isn't it possible to introduce a special type to > say something like: setObject(..., Types.UNKNOWN), I expect you can do this already with a PGobject implementantion. It might be nice to make it directly support String too. -O
Tom Lane wrote: > My previous response was based on what was in my inbox, which I now see > wasn't the whole thread. I agree with you that if we make setString > default to UNKNOWN, there had better be a way to say "by golly this > really is TEXT" for the corner cases. It'd be a good idea if it wasn't > limited to TEXT, either, but could allow specification of any random > datatype. We already have a mechanism for setting values of arbitary type: pass a PGobject implementation to setObject(). We could extend this easily so that passing a String with Types.UNKNOWN is a shorthand for passing a PGobject with a type of 'unknown'. It seems very odd to have a situation where the standard JDBC way of setting a VARCHAR parameter -- setString -- sometimes doesn't work and you have to go and use a non-standardi postgresql-specific method to say "no, really, this *is* VARCHAR". If we change setString() then we've gone from the existing case where correct applications work and broken applications break, to a situation where some correct applications do *not* work and must add this nonstandard code to continue to work, and broken applications have the breakage hidden. I can understand why people want this from the perspective of supporting existing apps that expect implicit casting of string parameters, but from a driver-correctness and new-development perspective it seems all wrong. Maybe a driver URL parameter that changes the behaviour of setString() is a reasonable compromise between the two? -O
Dave Cramer wrote: > For that matter we could use a PGUnknown type as well. This is exactly what the patch I mentioned earlier did, IIRC. -O
Christian is perfectly happy with a PGTimestamp, and a PGTimestamptz type. I did some research and Oracle has an extended PreparedStatement, which implements setTimestamp, and setTimestamptz, so I imagine they have run into the same issues Dave On 19-Jul-05, at 6:59 PM, Oliver Jowett wrote: > Dave Cramer wrote: > >> For that matter we could use a PGUnknown type as well. >> > > This is exactly what the patch I mentioned earlier did, IIRC. > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > >
Well, Oliver and Tom are very deep into things from their perspective, and I just wanted to offer an opinion from the legions of "app" developers. My opinion might not be what you expect, since I care less about compatability than some other things. What I care about most is that: 1) The conventions for standards-based constructs follow standards-based rules of engagement, so that if I ever get a clue about what the standard is, my code will be portable. 2) The code perform like screamin' demons. If setting it to UNKNOWN slows down all my parameterized query invocations, I'd rather not see that. 3) If setString() used to work for timestamps and doesn't work now because of standards compliance changes, please just tutor me on the correct standards based call to make. 4) If the combined JDBC/backend stuff, when used correctly, is screwing up timezones, that's mission critical and needs to be fixed. As far as application portability, I suspect most purveyors of heavy weight database apps view most any software upgrade, much less a complete vendor change, to be a "platform port" and so can fix these little incompatibilities as they debug the rest of their incompatibilities. (Whether it's adding "WITHOUT OIDS" to my pgsql CREATE TABLE statements for or "TYPE=INNODB" in futile attempts to get MySQL to give me transactionally safe tables). Heck, I'm still using 7.3. I skipped 7.4 because of transactions being stale on pooled connections, and I've been axiously looking forward to 8.0, but not if my timezones are shot to hell. In all this discussion I seem to have missed whether they're actually broken or whether I just need to make a "correct" call. Thanks for listening, and any further clarifications about my confusions are welcome.
On 19-Jul-05, at 7:11 PM, Jeffrey Tenny wrote:
Well, Oliver and Tom are very deep into things from their perspective,and I just wanted to offer an opinion from the legions of "app" developers. My opinion might not be what you expect, since I care less about compatability than some other things.What I care about most is that:1) The conventions for standards-based constructs followstandards-based rules of engagement, so that if I everget a clue about what the standard is, my code will be portable.
There is considerable ambiguity in the standard, and other drivers may implement
differently.
The challenge is this:
You wouldn't notice the affect of this.2) The code perform like screamin' demons. If setting it toUNKNOWN slows down all my parameterized query invocations,I'd rather not see that.
setTimestamp is the correct call to make3) If setString() used to work for timestamps and doesn't worknow because of standards compliance changes,please just tutor me on the correct standards based call to make.
4) If the combined JDBC/backend stuff, when used correctly, isscrewing up timezones, that's mission critical and needs to be fixed.
There is only one setTimestamp and there are two types of timestamps
1) timestamp with time zone
2) timestamp without time zone
Currently setTimestamp binds the parameter to timestamp with time zone (timestamptz), the
problem arises when the underlying data type is a timestamp without time zone (timestamp)
The backend automatically casts the timestamptz to a timestamp as per the comments in the
backend code
/* timestamptz_timestamp()
* Convert timestamp at GMT to local timestamp
*/
* Convert timestamp at GMT to local timestamp
*/
Dave
As far as application portability, I suspect most purveyors ofheavy weight database apps view most any software upgrade, much less a complete vendor change, to be a "platform port" and so can fix these little incompatibilities as they debug the rest of their incompatibilities. (Whether it's adding "WITHOUT OIDS" to my pgsql CREATE TABLE statements for or "TYPE=INNODB" in futile attempts to get MySQL to give me transactionally safe tables).Heck, I'm still using 7.3. I skipped 7.4 because of transactions being stale on pooled connections, and I've been axiously looking forward to 8.0, but not if my timezones are shot to hell. In all this discussion Iseem to have missed whether they're actually broken or whether I just need to make a "correct" call.Thanks for listening, and any further clarifications about my confusions are welcome.---------------------------(end of broadcast)---------------------------TIP 2: Don't 'kill -9' the postmaster
Christian Cryder wrote: > From my perspective, there needs to be a way for the JDBC driver to > know what type of timestamp you are working with. [...] How about this mapping? setTimestamp(int,Timestamp) -> timestamp setTimestamp(int,Timestamp,Calendar) -> timestamptz -O
Christian suggested this: However I think this too opaque. Not to mention the fact that it changes the current behaviour. Dave On 19-Jul-05, at 9:41 PM, Oliver Jowett wrote: > Christian Cryder wrote: > > >> From my perspective, there needs to be a way for the JDBC driver to >> know what type of timestamp you are working with. [...] >> > > How about this mapping? > > setTimestamp(int,Timestamp) -> timestamp > setTimestamp(int,Timestamp,Calendar) -> timestamptz > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > >
Dave Cramer wrote: > Christian suggested this: > > However I think this too opaque. Why do you think this? There's no timezone information associated with a Timestamp, so it seems like the logical mapping: if you provide a timezone via Calendar, it's a timestamp-with-timezone; otherwise, it's a timestamp-without-timezone. > Not to mention the fact that it changes the > current behaviour. Err, given that the current behaviour is broken, is this a problem? Every time I've looked at the timestamp code I've gone "ow, that has to be broken" but never got around to investigating further .. IMO, this is an area that the driver just gets *wrong* and we should be fixing it so it works, not trying to support applications that expect the wrong behaviour! -O
On 19-Jul-05, at 10:02 PM, Oliver Jowett wrote: > Dave Cramer wrote: > >> Christian suggested this: >> >> However I think this too opaque. >> > > Why do you think this? There's no timezone information associated > with a > Timestamp, so it seems like the logical mapping: if you provide a > timezone via Calendar, it's a timestamp-with-timezone; otherwise, > it's a > timestamp-without-timezone. Well, we're in a vague area of the spec here. There are two TIMESTAMP types defined by the sql spec, and only one setTimestamp. There is no indication by the spec that this behaviour is the "right" behaviour. > > >> Not to mention the fact that it changes the >> current behaviour. >> > > Err, given that the current behaviour is broken, is this a problem? Well, depends on what we break by "fixing" it. I still have access to the box and can re-run the cts to make sure it still passes. > > Every time I've looked at the timestamp code I've gone "ow, that > has to > be broken" but never got around to investigating further .. IMO, > this is > an area that the driver just gets *wrong* and we should be fixing > it so > it works, not trying to support applications that expect the wrong > behaviour! > Interestingly enough I implemented PGTimestamp, and PGTimestamptz and ran his test case. It passed in both cases and did the right thing. I'm still investigating why. Dave > -O > >
It appears all we have to do is remove the addition of the timezone information in setTimestamp(n, ts) and leave it for setTimestamp(n, ts, cal) This only fails our internal tests at +- infinity which is tractable. Dave On 19-Jul-05, at 10:27 PM, Dave Cramer wrote: > > On 19-Jul-05, at 10:02 PM, Oliver Jowett wrote: > > >> Dave Cramer wrote: >> >> >>> Christian suggested this: >>> >>> However I think this too opaque. >>> >>> >> >> Why do you think this? There's no timezone information associated >> with a >> Timestamp, so it seems like the logical mapping: if you provide a >> timezone via Calendar, it's a timestamp-with-timezone; otherwise, >> it's a >> timestamp-without-timezone. >> > > Well, we're in a vague area of the spec here. There are two > TIMESTAMP types defined by the sql > spec, and only one setTimestamp. There is no indication by the spec > that this behaviour is the "right" behaviour. > > > >> >> >> >>> Not to mention the fact that it changes the >>> current behaviour. >>> >>> >> >> Err, given that the current behaviour is broken, is this a problem? >> > > Well, depends on what we break by "fixing" it. > I still have access to the box and can re-run the cts to make sure > it still passes. > > > >> >> Every time I've looked at the timestamp code I've gone "ow, that >> has to >> be broken" but never got around to investigating further .. IMO, >> this is >> an area that the driver just gets *wrong* and we should be fixing >> it so >> it works, not trying to support applications that expect the wrong >> behaviour! >> >> > Interestingly enough I implemented PGTimestamp, and PGTimestamptz > and ran his test case. > It passed in both cases and did the right thing. I'm still > investigating why. > > Dave > >> -O >> >> >> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >
Dave Cramer wrote: > Well, we're in a vague area of the spec here. There are two TIMESTAMP > types defined by the sql spec, right. > and only one setTimestamp. The SQL spec doesn't define setTimestamp at all. The JDBC spec defines *two* setTimestamp methods. > There is no indication by the spec > that this behaviour is the "right" behaviour. The javadoc for the with-Calendar variant is quite clear: >> Sets the designated parameter to the given java.sql.Timestamp value, >> using the given Calendar object. The driver uses the Calendar object to >> construct an SQL TIMESTAMP value, which the driver then sends to the >> database. With a Calendar object, the driver can calculate the timestamp >> taking into account a custom timezone. If no Calendar object is >> specified, the driver uses the default timezone, which is that of the >> virtual machine running the application. I think it's pretty clear that this variant must map to timestamptz. The javadoc for the no-Calendar variant is more vague: >> Sets the designated parameter to the given java.sql.Timestamp value. The >> driver converts this to an SQL TIMESTAMP value when it sends it to the >> database. I'm willing to take that as license to map it to timestamp as there's no mention of timezone at all, no way to supply one, and "SQL TIMESTAMP" defaults to WITHOUT TIME ZONE I believe. If you want to set a TIMESTAMP WITH TIMEZONE using the default timezone, the JDBC spec seems to want you to call setTimestamp(i, timestamp, null) (see the first javadoc excerpt above -- "if no calendar object is specified"). The current driver also does this for setTimestamp(i,timestamp), which is what I'd like to change. (and also fix setTimestamp(i,timestamp,calendar) to actually preserve the timezone info you give it rather than mashing it into the default timezone, but that's a separate issue..) -O
On Tue, 19 Jul 2005, Christian Cryder wrote: > I should not have to configure my server to turn daylight savings off > in order to get stuff to insert into the DB correctly (after all, > other things run on the same server). No, you just need to turn it off on that particular connection. This was my original suggestion on how to handle this issue. The attached patch and built jar files, pass your example test just fine. I've also put up your test as a standalone compilable file here. http://www.ejurka.com/pgsql/jars/cc/ I think this approach is better than trying to infer something from the particular arguments to setTimestamp because it should just work unless you are trying to do something tricky. Unless you are working with a whole bunch of different timezones on the same connection there is no need to do anything else. I mean, if the driver developers can't figure out the semantics of setTimestamp how can we expect end users to? Kris Jurka
Вложения
Kris Jurka wrote: > ! { "TimeZone", java.util.TimeZone.getDefault().getID()} Ew! How much existing code is that going to break? -O
On 19-Jul-05, at 11:00 PM, Oliver Jowett wrote: > Dave Cramer wrote: > > >> Well, we're in a vague area of the spec here. There are two TIMESTAMP >> types defined by the sql spec, >> > > right. > > >> and only one setTimestamp. >> > > The SQL spec doesn't define setTimestamp at all. The JDBC spec defines > *two* setTimestamp methods. This was implied... > > >> There is no indication by the spec >> that this behaviour is the "right" behaviour. >> > > The javadoc for the with-Calendar variant is quite clear: > > >>> Sets the designated parameter to the given java.sql.Timestamp value, >>> using the given Calendar object. The driver uses the Calendar >>> object to >>> construct an SQL TIMESTAMP value, which the driver then sends to the >>> database. With a Calendar object, the driver can calculate the >>> timestamp >>> taking into account a custom timezone. If no Calendar object is >>> specified, the driver uses the default timezone, which is that of >>> the >>> virtual machine running the application. >>> > > I think it's pretty clear that this variant must map to timestamptz. Well to add more confusion here is the excerpt from Sun's JDBC API Tutorial pg 1024 ( slightly condensed ) The number of milliseconds in a Timestamp object always takes into account a time zone ... In order to calculate the milliseconds the driver takes into account the time zone, information the DBMS may or may not store. If no Calendar object is supplied the driver will use the default Calendar whose time zone is that of the JVM that is running the application. If the DBMS does provide timezone information the driver will simply use that and IGNORE (my caps) a Calendar object that may have been passed to it. > > The javadoc for the no-Calendar variant is more vague: > > >>> Sets the designated parameter to the given java.sql.Timestamp >>> value. The >>> driver converts this to an SQL TIMESTAMP value when it sends it >>> to the >>> database. >>> > > I'm willing to take that as license to map it to timestamp as > there's no > mention of timezone at all, no way to supply one, and "SQL TIMESTAMP" > defaults to WITHOUT TIME ZONE I believe. > > If you want to set a TIMESTAMP WITH TIMEZONE using the default > timezone, > the JDBC spec seems to want you to call setTimestamp(i, timestamp, > null) > (see the first javadoc excerpt above -- "if no calendar object is > specified"). The current driver also does this for > setTimestamp(i,timestamp), which is what I'd like to change. > > (and also fix setTimestamp(i,timestamp,calendar) to actually preserve > the timezone info you give it rather than mashing it into the default > timezone, but that's a separate issue..) > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > >
Thinking some more.... It would appear that the setTimestamp is intended to do one or the other, but not both. either you have timezone information or you don't. I think this effectively revokes your license to interpret the spec as you suggested below. Further the JDBCCTS doesn't take have any facility for both scenarios, you can choose the timestamp type that you want it to pass with (assuming you have a choice). Dave On 20-Jul-05, at 5:55 AM, Dave Cramer wrote: > > On 19-Jul-05, at 11:00 PM, Oliver Jowett wrote: > > >> Dave Cramer wrote: >> >> >> >>> Well, we're in a vague area of the spec here. There are two >>> TIMESTAMP >>> types defined by the sql spec, >>> >>> >> >> right. >> >> >> >>> and only one setTimestamp. >>> >>> >> >> The SQL spec doesn't define setTimestamp at all. The JDBC spec >> defines >> *two* setTimestamp methods. >> > This was implied... > >> >> >> >>> There is no indication by the spec >>> that this behaviour is the "right" behaviour. >>> >>> >> >> The javadoc for the with-Calendar variant is quite clear: >> >> >> >>>> Sets the designated parameter to the given java.sql.Timestamp >>>> value, >>>> using the given Calendar object. The driver uses the Calendar >>>> object to >>>> construct an SQL TIMESTAMP value, which the driver then sends to >>>> the >>>> database. With a Calendar object, the driver can calculate the >>>> timestamp >>>> taking into account a custom timezone. If no Calendar object is >>>> specified, the driver uses the default timezone, which is that >>>> of the >>>> virtual machine running the application. >>>> >>>> >> >> I think it's pretty clear that this variant must map to timestamptz. >> > > Well to add more confusion here is the excerpt from Sun's JDBC API > Tutorial > > pg 1024 ( slightly condensed ) > > > The number of milliseconds in a Timestamp object always takes into > account a time zone ... > > In order to calculate the milliseconds the driver takes into > account the time zone, information the DBMS may or may not store. > If no Calendar object is supplied the driver will use the default > Calendar whose time zone is that of the JVM that is > running the application. If the DBMS does provide timezone > information the driver will simply use that and IGNORE (my caps) a > Calendar object that may have been passed to it. > > > > >> >> The javadoc for the no-Calendar variant is more vague: >> >> >> >>>> Sets the designated parameter to the given java.sql.Timestamp >>>> value. The >>>> driver converts this to an SQL TIMESTAMP value when it sends it >>>> to the >>>> database. >>>> >>>> >> >> I'm willing to take that as license to map it to timestamp as >> there's no >> mention of timezone at all, no way to supply one, and "SQL TIMESTAMP" >> defaults to WITHOUT TIME ZONE I believe. >> >> If you want to set a TIMESTAMP WITH TIMEZONE using the default >> timezone, >> the JDBC spec seems to want you to call setTimestamp(i, timestamp, >> null) >> (see the first javadoc excerpt above -- "if no calendar object is >> specified"). The current driver also does this for >> setTimestamp(i,timestamp), which is what I'd like to change. >> >> (and also fix setTimestamp(i,timestamp,calendar) to actually preserve >> the timezone info you give it rather than mashing it into the default >> timezone, but that's a separate issue..) >> >> -O >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match >> >> >> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > > Dave Cramer davec@postgresintl.com www.postgresintl.com ICQ #14675561 jabber davecramer@jabber.org ph (519 939 0336 )
Dave Cramer wrote: > Thinking some more.... > > It would appear that the setTimestamp is intended to do one or the > other, but not both. > > either you have timezone information or you don't. I think this > effectively revokes your license to > interpret the spec as you suggested below. > > Further the JDBCCTS doesn't take have any facility for both scenarios, > you can choose the timestamp > type that you want it to pass with (assuming you have a choice). Ok, so this sounds like JDBC doesn't distinguish with-timezone and without-timezone at all so trying to base the semantics on the spec is hopeless; we should just pick something that's sensible and do that. I still think my suggestion of using the two setTimestamp methods to support the two types makes sense -- but I'm out of time to deal with this, sorry. -O
Dave Cramer wrote: > In order to calculate the milliseconds the driver takes into account > the time zone, information the DBMS may or may not store. If no > Calendar object is supplied the driver will use the default Calendar > whose time zone is that of the JVM that is > running the application. If the DBMS does provide timezone information > the driver will simply use that and IGNORE (my caps) a Calendar object > that may have been passed to it. Not having read the source material, but isn't this talking about the getTimestamp() path not the setTimestamp() path? -O
Yes, it does, my mistake ( It was early here ) However reading the notes about setTimestamp: When the DBMS does not store timezone information the driver will use cal to construct a JDBC Timestamp value. If no Calendar object is specified the driver uses the timezone of the JVM. Frustratingly it does not say what to do when the DBMS does not store timezone information. I guess one could infer that if it doesn't store timezone information, then timezone information is ignored ? Which puts us right back to the original problem... Two SQL types, and only one setTimestamp. Onel way to deal with this is to find out what the underlying type is, or to define a different server type, and cast to deal with this appropriately. IE Oid.javatimestamp and the cast would be able to do the right thing based on the underlying type. Dave On 20-Jul-05, at 8:30 AM, Oliver Jowett wrote: > Dave Cramer wrote: > > >> In order to calculate the milliseconds the driver takes into >> account the time zone, information the DBMS may or may not store. >> If no Calendar object is supplied the driver will use the default >> Calendar whose time zone is that of the JVM that is >> running the application. If the DBMS does provide timezone >> information the driver will simply use that and IGNORE (my caps) >> a Calendar object that may have been passed to it. >> > > Not having read the source material, but isn't this talking about > the getTimestamp() path not the setTimestamp() path? > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > >
Dave Cramer wrote: > Yes, it does, my mistake ( It was early here ) > > However reading the notes about setTimestamp: > > When the DBMS does not store timezone information the driver will use > cal to construct a JDBC Timestamp value. > If no Calendar object is specified the driver uses the timezone of the > JVM. Where's this from exactly? It doesn't seem to make sense if it's talking about setTimestamp -- the driver does not construct a JDBC timestamp in setTimestamp at all, it's given one by the application. -O
Same book JDBC API Tutorial 3rd Edition pg 676 In fact we do this now.. On 20-Jul-05, at 8:53 AM, Oliver Jowett wrote: > Dave Cramer wrote: > >> Yes, it does, my mistake ( It was early here ) >> However reading the notes about setTimestamp: >> When the DBMS does not store timezone information the driver will >> use cal to construct a JDBC Timestamp value. >> If no Calendar object is specified the driver uses the timezone >> of the JVM. >> > > Where's this from exactly? It doesn't seem to make sense if it's > talking about setTimestamp -- the driver does not construct a JDBC > timestamp in setTimestamp at all, it's given one by the application. > > -O > >
Holy schmoly, there's a lot of reading to catch up on here. Couple of comments as I try to take it all in... On 7/20/05, Oliver Jowett <oliver@opencloud.com> wrote: > Ok, so this sounds like JDBC doesn't distinguish with-timezone and > without-timezone at all so trying to base the semantics on the spec is > hopeless; we should just pick something that's sensible and do that. > > I still think my suggestion of using the two setTimestamp methods to > support the two types makes sense -- but I'm out of time to deal with > this, sorry. I like Oliver's suggestion (because it would work for me) even though I think Dave is probably right when he says > > It would appear that the setTimestamp is intended to do one or the > > other, but not both. One issue which no one has really mentioned is that when we flatten a timestamp to a string via toString() (eg. I ran into this even when creating a custom PGTimestamp object yesterday) - when you flatten to a String, you ARE using a calendar whether you realize it or not (eg. the Default). Which means that you are performing a DST shift on the client whether you like it or not. So if the date falls into the right segment, it may still get munged. The point here is that not only do we need to consider how to pass the value to the server w/ TIMESTAMP rather than TIMESTAMPTZ, we also need to consider how to render it to a string in PGobject.value w/out munging the date. And the problem here (I think) is that you really need to know the column info (w/ tz or w/out) in order to know whether DST applies. So that's something that needs to be considered. Right now, I can work around the existing problems using PGTimestamp (per Dave's suggestion), but I still have to tweak the timezone I am running in to turn DST off in order to keep the dates from getting munged when rendered to strings. One other thing - could the problem be alleviated simply by a) sending the data across the wire, always using UNKNOWN (thereby deferring the decision to the DB) b) sending the data across as millisecs value, rather than flattening to a Timestamp string? That way to could avoid the toString() issues mentioned above, plus it'd probably be faster to reconsitute from millis on the server than by parsing a timestamp string anyways. Those are just some thoughts to consider... Christian
One other comment - if it was me implementing this thing from scratch, here's how I'd be inclined to tackle it... a) I'd send the timestamp value across the wire as millis (the millis value won't change based on whether DST is turned on/off w/in the default zone on the client) - so reading/sending as millis would mean that the client wouldn't end up munging any dates unintentionally simply on the basis of DST (which is a problem no) b) I would interpret setTimestamp(i, ts, cal) as meaning "convert from current millis to millis in cal's timezone", and then send that new millis value across the wire c) I'd send the timestamp across the wire as UNKNOWN, and then allow the server to make the decision based on the column type - since the server knows what the column is defined as, it can easily decide how to interpret the millis value when figuring out how to actually persist it It seems to me this would handle all the problems we are having, while working within the API limitations. It doesn't seem like it would break any existing code (at least not anything that's not already broken). Of course, I have no idea whether any of this is feasible or not. But I think the root of our problems is tied to flattening timestamp objects via toString(), which then applies the calendar rendering, which then applies DST rules of the default timezone (again, whether you want them to or not). One final consideration - has anyone evaluated the performance implications of rendering/reconstituting timestamps to Strings vs. millis? It seems to me like there must be a lot more overhead associated with the String versions (looking up calendars, applying dst logic, etc) than with millis. Christian
On Wed, 20 Jul 2005, Oliver Jowett wrote: > Kris Jurka wrote: > > > ! { "TimeZone", java.util.TimeZone.getDefault().getID()} > > Ew! How much existing code is that going to break? > Very little. Currently everything works if the client and server are in the same timezone which is 99% of the time. This just ensures that the client and server agree on what timezone to use for the remaining 1% which are already broken. Problems arise when the server and JDK don't have the same set of timezones, which will happen, but not often. We could easily add a URL parameter to bail people out of here though. http://archives.postgresql.org/pgsql-jdbc/2004-12/msg00139.php Kris Jurka
On 7/20/05, Kris Jurka <books@ejurka.com> wrote: > Currently everything works if the client and server are in > the same timezone which is 99% of the time. Just to clarify - this is not technically correct. If you have a zoneless timestamp in the db, and your client and server are running in daylight savings time, and that date happens to fall in DST "no man's land" (eg. between 1 and 2 AM on fist Sunday in April), that value WILL get munged when you read it, and it stays munged when you write it back - regardless of how the column is declared in the db. This is a function of the timestamp getting flattened to a String (which in turn uses a Calendar, which in turn applies DST to display a "valid" time, which in turn munges the data). So there is a scenario where the dates will still get munged even though client and server are in the same timezone. And that's a problem. Christian
Kris Jurka <books@ejurka.com> writes: > ... Problems arise when the server and JDK don't have the > same set of timezones, which will happen, but not often. We could easily > add a URL parameter to bail people out of here though. Hmm ... does Java have a standard set of timezone names? If so, does it bear any resemblance to the zic database names? regards, tom lane
On 7/20/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Hmm ... does Java have a standard set of timezone names? If so, does it > bear any resemblance to the zic database names? Yes, you can see them by running this snippet... String zones[] = TimeZone.getAvailableIDs(); for (int i=0; i<zones.length; i++) { System.out.println(" "+i+": "+TimeZone.getTimeZone(zones[i])); } No idea how that compares to zic... Christian
Yes, it does, and yes they should be. The real solution appears to be to use the current server timezone, not the other way around. See my other posts. Dave On 20-Jul-05, at 1:41 PM, Tom Lane wrote: > Kris Jurka <books@ejurka.com> writes: > >> ... Problems arise when the server and JDK don't have the >> same set of timezones, which will happen, but not often. We could >> easily >> add a URL parameter to bail people out of here though. >> > > Hmm ... does Java have a standard set of timezone names? If so, > does it > bear any resemblance to the zic database names? > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > >
On Wednesday 20 July 2005 13:41, Tom Lane wrote: > Hmm ... does Java have a standard set of timezone names? If so, > does it bear any resemblance to the zic database names? I'm not sure how Roedy Green knows this, but this is what he has to say on the subject in his Java Glossary: http://www.mindprod.com/jgloss/timezone.html The names for timezones used in Java comes from a list maintained at NIH by Arthur David Olson. For reasons only he understands, Pacific Standard Time is called America/Los_Angeles. As far as I can tell, Olson's timezone data can be found here: ftp://elsie.nci.nih.gov/pub/ Sun's JDK puts timezone data in $JAVA_HOME/jre/lib/zi/ Sun's JDK's timezone info seems fairly different from what, say, Fedora Core distributes in its tzdata RPM: | $ find /usr/share/zoneinfo/ -type f -printf '%P\n' | sort > /tmp/linux-tzdata.txt | $ find /usr/local/j2sdk1.4.2_08/jre/lib/zi -type f -printf '%P\n' | sort > /tmp/java-tzdata.txt | $ diff /tmp/linux-tzdata.txt /tmp/java-tzdata.txt | wc -l | 1309
Actually, Java has timezones that postgres doesn't understand, which is why using the server timezone makes more sense. Hopefully java understands all the timezones postgres understands. Dave On 20-Jul-05, at 1:47 PM, Christian Cryder wrote: > On 7/20/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Hmm ... does Java have a standard set of timezone names? If so, >> does it >> bear any resemblance to the zic database names? >> > > Yes, you can see them by running this snippet... > > String zones[] = TimeZone.getAvailableIDs(); > for (int i=0; i<zones.length; i++) { > System.out.println(" "+i+": "+TimeZone.getTimeZone(zones[i])); > } > > No idea how that compares to zic... > > Christian > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >
Vadim Nasardinov <vadimn@redhat.com> writes: > I'm not sure how Roedy Green knows this, but this is what he has to > say on the subject in his Java Glossary: > http://www.mindprod.com/jgloss/timezone.html > The names for timezones used in Java comes from a list maintained > at NIH by Arthur David Olson. For reasons only he understands, > Pacific Standard Time is called America/Los_Angeles. > As far as I can tell, Olson's timezone data can be found here: > ftp://elsie.nci.nih.gov/pub/ Hmm ... that is the zic distribution, so if this information is accurate there should be a pretty exact match in the sets of names ... which it sounds like there is not. > Sun's JDK's timezone info seems fairly different from what, say, > Fedora Core distributes in its tzdata RPM: Fedora's info also comes from zic. regards, tom lane
On Wednesday 20 July 2005 11:40, Christian Cryder wrote: > b) sending the data across as millisecs value, rather than > flattening to a Timestamp string? That way to could avoid the > toString() issues mentioned above, plus it'd probably be faster to > reconsitute from millis on the server than by parsing a timestamp > string anyways. I learned all sorts of fascinating things by reading this thread: http://forum.java.sun.com/thread.jspa?messageID=2813701 For example: | $ export TZ=Europe/London | $ date +%s -d '19981231 23:59:00' | 915148740 | $ date +%s -d '19990101 00:00:00' | 915148800 | | We can see here that the duration of the last minute in 1998 was 60 | seconds. | | The more or less experimental time zones prefixed by "right/" | accounts for leap seconds: | | $ export TZ=right/Europe/London | $ date +%s -d '19981231 23:59:00' | 915148761 | $ date +%s -d '19990101 00:00:00' | 915148822 | | We see here that the same last minute was 61 seconds long. | | The implementation of Java by Sun does not retrieve the leap | seconds info from the C library. | | As leap second support seems not to have been implemented yet, | every minute in Sun's Java seems to be 60 seconds long. | | But there is no guarantee it won't change in the future, as | permitted in the documentation, and other implementations of Java | can be different, too. Based on this, I get the vague impression that Java's interpretation of milliseconds since epoch may differ from PostgreSQL's interpretation of the same.
On 20-Jul-05, at 2:29 PM, Tom Lane wrote: > Vadim Nasardinov <vadimn@redhat.com> writes: > >> I'm not sure how Roedy Green knows this, but this is what he has to >> say on the subject in his Java Glossary: >> > > >> http://www.mindprod.com/jgloss/timezone.html >> > > >> The names for timezones used in Java comes from a list maintained >> at NIH by Arthur David Olson. For reasons only he understands, >> Pacific Standard Time is called America/Los_Angeles. >> > > >> As far as I can tell, Olson's timezone data can be found here: >> ftp://elsie.nci.nih.gov/pub/ >> > > Hmm ... that is the zic distribution, so if this information is > accurate > there should be a pretty exact match in the sets of names ... which it > sounds like there is not. On my Mac my java timezone was set to Canada/Montreal which was broken (did not use DST ) and the server certainly doesn't understand it. The server on the same machine had the timezone set to Canada/Eastern which java understands and I hacked the driver code to use the server timezone. This seems to work. There's still a fair bit of ambiguity as to whether java will do the right thing with the server supplied time zones though? It appears that timestamp, date, calendar in java are all terribly implemented. I'm starting to see more and more why other DBMS implemented their own setTimestamptz. > > >> Sun's JDK's timezone info seems fairly different from what, say, >> Fedora Core distributes in its tzdata RPM: >> > > Fedora's info also comes from zic. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > >
On Wednesday 20 July 2005 14:57, Dave Cramer wrote: > On my Mac my java timezone was set to Canada/Montreal which was > broken (did not use DST ) and the server certainly doesn't > understand it. Out of curiosity, do you remember which JDK had this timezone? Sun's JDK on Linux doesn't have it: | $ find /usr/local/j2sdk1.4.2_08/jre/lib/zi/ -name Montreal | /usr/local/j2sdk1.4.2_08/jre/lib/zi/America/Montreal It only has America/Montreal, which PostgreSQL should grok just fine, AFAICT: | $ find /usr/share/zoneinfo/ -name Montreal | /usr/share/zoneinfo/right/America/Montreal | /usr/share/zoneinfo/posix/America/Montreal | /usr/share/zoneinfo/America/Montreal
Dave Cramer wrote: > Actually, > > Java has timezones that postgres doesn't understand, which is > why using the server timezone makes more sense. > > Hopefully java understands all the timezones postgres understands. as we discovered the hard way, named timezones are a BAD IDEA. We had some stuff in java + jdbc + postgres that used a timezone, when it was brought up in Singapore, their local timezone memnonic wasn't recognized by postgres, and when it was brought up in China, CST was misinterpretted as Central Standard Time rather than China Standard Time (about 12 hours off)
On Wednesday 20 July 2005 14:16, Vadim Nasardinov wrote: > Sun's JDK's timezone info seems fairly different from what, say, > Fedora Core distributes in its tzdata RPM: More precisely, Sun's timezone info seems to be a strict subset of Fedora's timezone info. (This is for Sun's JDK 1.4.2_08 and Fedora Core 3. I didn't bother to check JDK 1.5.0 -- it does add three additional timezones over what was present in 1.4.2: America/Bahia, America/Campo_Grande, and America/Toronto.) I added a couple of "grep -v" pipes to filter out irrelevant differences: | $ find /usr/local/j2sdk1.4.2_08/jre/lib/zi -type f -printf '%P\n' | \ | grep -v ZoneInfoMappings | sort > /tmp/java-tzdata.txt | $ find /usr/share/zoneinfo/ -type f -printf '%P\n' | \ | grep -v right/ | grep -vE '(right/|posix/|zone\.tab)' | \ | sort > /tmp/linux-tzdata.txt This shows that the FC3 tzdata is a strict superset of the JDK 1.4.2 timezone info: | $ diff /tmp/java-tzdata.txt /tmp/linux-tzdata.txt | grep -c '>' | 152 | $ diff /tmp/java-tzdata.txt /tmp/linux-tzdata.txt | grep -c '<' | 0 Whether or not this means that PostgreSQL on FC should be able to handle any time zone that Java throws at it, I am not sure.
Vadim, Actually you are correct, it is America/Montreal and now that I switched it back it actually has useDaylight=true... very strange. I think as John pointed out though there are some issues with using named TimeZones. Is there a way to get the servers timezone info from the server ? Dave On 20-Jul-05, at 3:10 PM, Vadim Nasardinov wrote: > On Wednesday 20 July 2005 14:57, Dave Cramer wrote: > >> On my Mac my java timezone was set to Canada/Montreal which was >> broken (did not use DST ) and the server certainly doesn't >> understand it. >> > > Out of curiosity, do you remember which JDK had this timezone? > Sun's JDK on Linux doesn't have it: > > | $ find /usr/local/j2sdk1.4.2_08/jre/lib/zi/ -name Montreal > | /usr/local/j2sdk1.4.2_08/jre/lib/zi/America/Montreal > > It only has America/Montreal, which PostgreSQL should grok just fine, > AFAICT: > > | $ find /usr/share/zoneinfo/ -name Montreal > | /usr/share/zoneinfo/right/America/Montreal > | /usr/share/zoneinfo/posix/America/Montreal > | /usr/share/zoneinfo/America/Montreal > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >
On Wed, 20 Jul 2005, John R Pierce wrote: > as we discovered the hard way, named timezones are a BAD IDEA. We had some > stuff in java + jdbc + postgres that used a timezone, when it was brought up in > Singapore, their local timezone memnonic wasn't recognized by postgres, and > when it was brought up in China, CST was misinterpretted as Central Standard > Time rather than China Standard Time (about 12 hours off) > No, this indicates that *abbreviated* timezones are a bad idea, not named timezones. Kris Jurka
I think using the same time zone as the server is the only way to go: Kris has proposed a patch which would set the servers time zone to the JVM when the connection is started This can still be broken if someone were to change the default timezone after the connection was initiated. If we do the reverse and save the servers timezone for the purposes of creating the timestamp object we don't run into this problem, even if someone were to issue a set timezone='newtimezone' we will still get the notice and can update the stored server timezone for the connection. Dave On 20-Jul-05, at 3:50 PM, Kris Jurka wrote: > > > On Wed, 20 Jul 2005, John R Pierce wrote: > > >> as we discovered the hard way, named timezones are a BAD IDEA. >> We had some >> stuff in java + jdbc + postgres that used a timezone, when it was >> brought up in >> Singapore, their local timezone memnonic wasn't recognized by >> postgres, and >> when it was brought up in China, CST was misinterpretted as >> Central Standard >> Time rather than China Standard Time (about 12 hours off) >> >> > > No, this indicates that *abbreviated* timezones are a bad idea, not > named > timezones. > > Kris Jurka > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > >
On Wednesday 20 July 2005 15:42, Dave Cramer wrote: > I think as John pointed out though there are some issues with using > named TimeZones. Kris rebutted that comment. > Is there a way to get the servers timezone info from the server ? Good question. Based upon a very cursory reading of the documentation, it seems to be that PostgreSQL uses a hardcoded internal list of time zones for dealing with timezone *input*, but it uses the underlying OS's facilities for dealing with timezone *output*. But then again, the docs seems to have changed between 7.4 and 8.0. For example, in 7.4: http://www.postgresql.org/docs/7.4/static/datatype-datetime.html PostgreSQL uses your operating system's underlying features to provide output time-zone support, However, this sentence seems missing from 8.0: http://www.postgresql.org/docs/8.0/static/datatype-datetime.html The docs for 7.4 provide a list of timezone abbreviations that PostgreSQL recognizes: http://www.postgresql.org/docs/7.4/static/datetime-keywords.html The 8.0 docs, too, provide the list of abbreviations but also a list of unabbreviated timezone names: http://www.postgresql.org/docs/8.0/static/datetime-keywords.html These seem to be hardcoded.
On 7/20/05, Dave Cramer <pg@fastcrypt.com> wrote: > I think using the same time zone as the server is the only way to go: How does this prevent the DST munging issue, where a zoneless time (eg. 04-03-2005 1:22 AM) is read from the DB and then re-written? What is to keep it being from being rewritten as 2:22 AM? > Kris has proposed a patch which would set the servers time zone to > the JVM when the connection is started What happens when someone writes a client server app, and one client connects from timezone A, and another client connects from timezone B, and the server itself is running in timezone C? Christian
On 20-Jul-05, at 4:44 PM, Christian Cryder wrote: > On 7/20/05, Dave Cramer <pg@fastcrypt.com> wrote: > >> I think using the same time zone as the server is the only way to go: >> > > How does this prevent the DST munging issue, where a zoneless time > (eg. 04-03-2005 1:22 AM) is read from the DB and then re-written? What > is to keep it being from being rewritten as 2:22 AM? We still have the crossover to deal with > > >> Kris has proposed a patch which would set the servers time zone to >> the JVM when the connection is started >> > > What happens when someone writes a client server app, and one client > connects from timezone A, and another client connects from timezone B, > and the server itself is running in timezone C? > Setting the servers time zone only sets the time zone for that client. > Christian > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > >
Vadim Nasardinov <vadimn@redhat.com> writes: > Based upon a very cursory reading of the documentation, it seems to be > that PostgreSQL uses a hardcoded internal list of time zones for > dealing with timezone *input*, but it uses the underlying OS's > facilities for dealing with timezone *output*. But then again, the > docs seems to have changed between 7.4 and 8.0. That's because the underlying implementation was completely revamped. We now have our own timezone code and timezone database (the latter being the zic database) to avoid depending on the OS facilities. regards, tom lane
On Wednesday 20 July 2005 18:33, Tom Lane wrote: > That's because the underlying implementation was completely > revamped. We now have our own timezone code and timezone database > (the latter being the zic database) to avoid depending on the OS > facilities. If 8.0 comes with its own copy of a recent zic database, then, in theory, it should be able to handle any timezone known to Java. (The opposite is not necessarily true, as Java's timezone info seems to be missing a lot of recent changes.) Does that sound like a correct statement to you or did I forget some important qualifiers?
Vadim Nasardinov <vadimn@redhat.com> writes: > If 8.0 comes with its own copy of a recent zic database, then, in > theory, it should be able to handle any timezone known to Java. (The > opposite is not necessarily true, as Java's timezone info seems to be > missing a lot of recent changes.) Does that sound like a correct > statement to you or did I forget some important qualifiers? Yeah, it seems that we are just open to version skew issues between different copies of the zic database ... which changes enough that that's a nontrivial risk, but I fear we are unlikely to do better. In any case, it seems that the arguments about which side is likely to know more timezone names than the other side are moot; we should not let that consideration drive the design choice. regards, tom lane
Kris Jurka wrote: > > On Wed, 20 Jul 2005, Oliver Jowett wrote: > > >>Kris Jurka wrote: >> >> >>>! { "TimeZone", java.util.TimeZone.getDefault().getID()} >> >>Ew! How much existing code is that going to break? > > Very little. Aside from the issues that others have raised, I was worried about existing code that does *not* use PreparedStatement. (probably with good reason given this discussion!) I really don't think the driver should be touching TimeZone :/ -O
Oliver Jowett <oliver@opencloud.com> writes: > I really don't think the driver should be touching TimeZone :/ The idea of tracking the server timezone and using it locally for conversions of incoming data would avoid that gripe. I am not sure however that Christian's basic complaint is solvable. The problem here is very simple: the semantics of SQL TIMESTAMP WITHOUT TIME ZONE do not match Java's Timestamp, no how, no way. For instance, '2004-04-04 02:30' is an unconditionally valid timestamp-without-zone, no matter what anybody's timezone setting is. However, if you try to interpret it as local time in a US DST-observing zone, you have a problem. You can never convert this value to timestamptz and back in a DST-observing zone and not have it change ... which is basically what Christian is hoping for. But there is *no* timestamptz value that will decode as 02:30, because that's not a valid value for timestamptz. I think any solution that tries to work 100% for both flavors of SQL timestamp is simply doomed to failure --- unless there are more semantics to Java's Timestamp type than I've gathered from this discussion. The impression I have is that Timestamp is supposed to represent absolute time instants (ie, there's no additional "what time zone is this in" info needed to determine the exact equivalent GMT time), which would make it equivalent to timestamptz. timestamp-without-zone is a fundamentally different critter, because it does not assume that there is any such thing as absolute GMT-equivalent time. regards, tom lane
On Thu, 21 Jul 2005, Oliver Jowett wrote: > Aside from the issues that others have raised, I was worried about > existing code that does *not* use PreparedStatement. (probably with good > reason given this discussion!) > > I really don't think the driver should be touching TimeZone :/ > You might appreciate this archived message... http://archives.postgresql.org/pgsql-jdbc/2004-12/msg00169.php Kris Jurka
Kris Jurka wrote: > > On Thu, 21 Jul 2005, Oliver Jowett wrote: > >>Aside from the issues that others have raised, I was worried about >>existing code that does *not* use PreparedStatement. (probably with good >>reason given this discussion!) >> >>I really don't think the driver should be touching TimeZone :/ > > You might appreciate this archived message... > > http://archives.postgresql.org/pgsql-jdbc/2004-12/msg00169.php Heh, good point :-) Of course there are no broken applications out there! ;-) I still think touching TimeZone is asking for trouble -- we've got no guarantees that the Java and server timezone databases are 100% in sync (even for those timezones in common) -O
Tom Lane wrote: > I think any solution that tries to work 100% for both flavors of SQL > timestamp is simply doomed to failure --- unless there are more > semantics to Java's Timestamp type than I've gathered from this > discussion. The impression I have is that Timestamp is supposed to > represent absolute time instants (ie, there's no additional "what time > zone is this in" info needed to determine the exact equivalent GMT > time), which would make it equivalent to timestamptz. That's correct. A Timestamp is essentially a wrapper around milliseconds-since-epoch. It has no timezone information beyond some badly thought out, not settable, "timezone offset" that always reflects the default timezone, and some getHour/getMinute/etc accessors (again using the JVM's default timezone only); these have been deprecated since JDK 1.1 in favor of using Calendar objects. Timestamps are then interpreted by feeding them through a Calendar for locale- and timezone-specific formatting -- e.g. you can ask a Calendar for the hour of a particular Timestamp, and it'll give you the appropriate hour based on the timezone the Calendar is for. > timestamp-without-zone is a fundamentally different critter, because it > does not assume that there is any such thing as absolute GMT-equivalent > time. In other words, a timestamp-without-zone is just a year-month-day-hour-minute-second tuple, which could correspond to many instants in time depending on timezone and daylight savings changes? The thing is that there are two distinct variants of setTimestamp(): (1) setTimestamp(index, timestamp) (2) setTimestamp(index, timestamp, calendar) (2) obviously maps to timestamp-with-zone. The question is what do we do with (1) -- is the intention to set: (a) a particular instant in time identified by 'timestamp', or (b) the particular year-month-day-hour-minute-second described by ('timestamp' interpreted in the default timezone of the JVM) 1(a) is timestamp-with-zone again 1(b) is timestamp-without-zone The JDBC spec doesn't help us here. If we go with 1(a) then we have problems when casting to a timestamp-without-zone value when the JVM and server timezones do not match. If we go with 1(b) then we have problems when casting to a timestamp-with-zone value when the JVM and server timezones do not match, or when daylight savings means there are two possible instants in a particular timezone identified by the timestamp-without-zone. ... I'd like to go with 1(b): I don't like 1(a) because it gives you no way of getting a timestamp-without-zone into the database without having a timezone available on the Java side that exactly matches the server timezone. As found elsewhere in the thread, the two zic databases are already out of sync, so I don't like the chances of that.. With 1(b), if you hit the casting problem, you have a workaround: use the setTimestamp() method that takes a Calendar, and explicitly pass the default JVM calendar. That completely avoids the need for the problematic cast. -O
On 20-Jul-05, at 8:16 PM, Oliver Jowett wrote: > Kris Jurka wrote: > >> >> On Thu, 21 Jul 2005, Oliver Jowett wrote: >> >> >>> Aside from the issues that others have raised, I was worried about >>> existing code that does *not* use PreparedStatement. (probably >>> with good >>> reason given this discussion!) >>> >>> I really don't think the driver should be touching TimeZone :/ >>> >> >> You might appreciate this archived message... >> >> http://archives.postgresql.org/pgsql-jdbc/2004-12/msg00169.php >> > > Heh, good point :-) > > Of course there are no broken applications out there! ;-) > > I still think touching TimeZone is asking for trouble -- we've got no > guarantees that the Java and server timezone databases are 100% in > sync > (even for those timezones in common) If we track the server's timezone in the driver, then if we get a timezone that we don't understand we can just use the JVM's default time zone. > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > >
Oliver Jowett <oliver@opencloud.com> writes: > That's correct. A Timestamp is essentially a wrapper around > milliseconds-since-epoch. OK --- that is also our interpretation of TIMESTAMP WITH TIME ZONE (at the moment anyway --- there's some agitation for putting an explicit zone identifier in too, because it looks like the SQL spec requires that). > Timestamps are then interpreted by feeding them through a Calendar for > locale- and timezone-specific formatting -- e.g. you can ask a Calendar > for the hour of a particular Timestamp, and it'll give you the > appropriate hour based on the timezone the Calendar is for. Right, this corresponds to the server's idea of rotating the timestamptz value to the client's TimeZone for display. > In other words, a timestamp-without-zone is just a > year-month-day-hour-minute-second tuple, which could correspond to many > instants in time depending on timezone and daylight savings changes? Exactly. This I believe is per SQL spec. > The thing is that there are two distinct variants of setTimestamp(): > (1) setTimestamp(index, timestamp) > (2) setTimestamp(index, timestamp, calendar) > (2) obviously maps to timestamp-with-zone. Hm, that's not obvious to me at all. If the timestamp is supposed to be unconditional absolute time, then the only sensible interpretation of (1) is that you're setting a timestamptz, and (2) would presumably produce a timestamp-without-tz value corresponding to the local time readout of the Calendar. > If we go with 1(a) then we have problems when casting to a > timestamp-without-zone value when the JVM and server timezones do not match. > If we go with 1(b) then we have problems when casting to a > timestamp-with-zone value when the JVM and server timezones do not > match, or when daylight savings means there are two possible instants in > a particular timezone identified by the timestamp-without-zone. No matter what we do, we will have issues if the user uses the wrong variant of setTimestamp for a particular parameter --- the server will add its own rotation by the TimeZone offset while converting timestamp to timestamptz or vice versa. You could possibly avoid that gotcha by setting the server's TimeZone to UTC, but I got the impression you wanted to avoid doing that. regards, tom lane
Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> writes: > >>That's correct. A Timestamp is essentially a wrapper around >>milliseconds-since-epoch. > > OK --- that is also our interpretation of TIMESTAMP WITH TIME ZONE > (at the moment anyway --- there's some agitation for putting an > explicit zone identifier in too, because it looks like the SQL spec > requires that). Ah ok, this is where some of my confusion is coming from -- I assumed that WITH TIME ZONE actually stored the timezone you provided and would preserve it on output. Guess I should test these things before wading in! >>The thing is that there are two distinct variants of setTimestamp(): > > >> (1) setTimestamp(index, timestamp) >> (2) setTimestamp(index, timestamp, calendar) > >>(2) obviously maps to timestamp-with-zone. > > Hm, that's not obvious to me at all. If the timestamp is supposed to be > unconditional absolute time, then the only sensible interpretation of (1) > is that you're setting a timestamptz, and (2) would presumably produce > a timestamp-without-tz value corresponding to the local time readout of > the Calendar. I was coming at it from the representations of the values: '2005-01-01 15:00:00' is a timestamp representation '2005-01-01 15:00:00+1300' is a timestamptz representation We don't have a timezone offset to send in case (1) so it "must" be a timestamp. But I can see where your interpretation comes from though -- if the Timestamp is defined to be milliseconds-since-epoch we can identify the instant without needing a separate timezone offset. Reviewing it all I'm leaning towards JDBC's idea of TIMESTAMP being timestamp-without-timezone; the driver is meant to convert the absolute-instant of the Timestamp to a particular date/time using a specified (or default) timezone. That interpretation would make some of the setTimestamp() javadoc more comprehensible. This would also explain why Oracle apparently has a separate extension setTimestamptz() method. Think I'm going to give up on this now though.. -O
Dave Cramer wrote: > If we track the server's timezone in the driver, then if we get a > timezone that we don't understand > we can just use the JVM's default time zone. Uh, how does this do anything but mangle your dates? -O
This is more or less the same as Kris's suggestion of setting the server's time zone to the JVM's time zone, except we can track time zone changes using the NOTICE messages. So if the user were to create a connection and then subsequently change the JVM's default time zone. Which isn't that far fetched if the user is using a connection pool. The driver would remain in sync with the server's time zone. On 20-Jul-05, at 9:09 PM, Oliver Jowett wrote: > Dave Cramer wrote: > > >> If we track the server's timezone in the driver, then if we get a >> timezone that we don't understand >> we can just use the JVM's default time zone. >> This is just a safe fallback position that's no worse than what we have now --dc-- > > Uh, how does this do anything but mangle your dates? > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > >
On 20-Jul-05, at 9:09 PM, Oliver Jowett wrote: > Tom Lane wrote: > >> Oliver Jowett <oliver@opencloud.com> writes: >> >> >>> That's correct. A Timestamp is essentially a wrapper around >>> milliseconds-since-epoch. >>> >> >> OK --- that is also our interpretation of TIMESTAMP WITH TIME ZONE >> (at the moment anyway --- there's some agitation for putting an >> explicit zone identifier in too, because it looks like the SQL spec >> requires that). Reading the JDBC Tutorial suggests that this information would be used when retrieving the timestamp. >> > > Ah ok, this is where some of my confusion is coming from -- I assumed > that WITH TIME ZONE actually stored the timezone you provided and > would > preserve it on output. Guess I should test these things before > wading in! > > >>> The thing is that there are two distinct variants of setTimestamp(): >>> >> >> >> >>> (1) setTimestamp(index, timestamp) >>> (2) setTimestamp(index, timestamp, calendar) >>> >> >> >>> (2) obviously maps to timestamp-with-zone. >>> >> >> Hm, that's not obvious to me at all. If the timestamp is supposed >> to be >> unconditional absolute time, then the only sensible interpretation >> of (1) >> is that you're setting a timestamptz, and (2) would presumably >> produce >> a timestamp-without-tz value corresponding to the local time >> readout of >> the Calendar. >> > > I was coming at it from the representations of the values: > > '2005-01-01 15:00:00' is a timestamp representation > '2005-01-01 15:00:00+1300' is a timestamptz representation > > We don't have a timezone offset to send in case (1) so it "must" be a > timestamp. But I can see where your interpretation comes from > though -- > if the Timestamp is defined to be milliseconds-since-epoch we can > identify the instant without needing a separate timezone offset. > > Reviewing it all I'm leaning towards JDBC's idea of TIMESTAMP being > timestamp-without-timezone; the driver is meant to convert the > absolute-instant of the Timestamp to a particular date/time using a > specified (or default) timezone. That interpretation would make > some of > the setTimestamp() javadoc more comprehensible. Well, there is documentation that suggests both are supported; however apparently not simultaneously. > > This would also explain why Oracle apparently has a separate extension > setTimestamptz() method. > > Think I'm going to give up on this now though.. > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that > your > message can get through to the mailing list cleanly > >
On 7/20/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I am not sure however that Christian's basic complaint is solvable. > The problem here is very simple: the semantics of SQL TIMESTAMP WITHOUT > TIME ZONE do not match Java's Timestamp, no how, no way. For instance, > '2004-04-04 02:30' is an unconditionally valid timestamp-without-zone, > no matter what anybody's timezone setting is. However, if you try > to interpret it as local time in a US DST-observing zone, you have a > problem. You can never convert this value to timestamptz and back in > a DST-observing zone and not have it change ... which is basically > what Christian is hoping for. But there is *no* timestamptz value > that will decode as 02:30, because that's not a valid value for > timestamptz. Hi guys, Here's what we're looking for in a nutshell. The DB allows a date of '2004-04-04 02:30' in a timestamp column (eg. it may have gotten there via a manual insert from the sql query tool, etc. - However it got there, it's there; that's our starting point). Now, all we want is to be able to read that value via JDBC and then write it back via JDBC, and have the DB end up with same date that we started w/ at the beginning. And right now, it's _extremely_ difficult to accomplish that via the current Postgres driver implementation (we had to jump through hoops and create a custom PGTimestamp class, then wrap the postgres prepared statement, and even then, we still have to programatically turn off DST on the client...it works, but BLECH!) In my mind, that fact that it is so difficult to do such a simple thing should give us pause; it's a fundamental data integrity issue at the root. It's certainly causing us a lot of grief as we try to think about migrating to Postgres; and I'll bet we're not the only ones who see this as an issue. Please don't misunderstand - I realize the JDBC spec is vague, I understand that Sun's Timestamp implementation may have been poorly thought out. But at the end of the day, we have a timestamp value in Postgres - we need to be able to read/write that value without having it changed on us. We are currently using another DB who's JDBC implementation doesn't have this problem. We currently have lots and lots of code using PreparedStatements (so switching to Statements to work around this is not very realistic). The idea of having to turn off DST on our severs in order to make this work is also not a viable option (there are other things running on these boxes, where the time does matter). My point in all this is that regardless of who's to blame here, what we really need is a workable solution. What I was trying to say yesterday is that I think a big part of the problem hinges on the decision to convert the underlying timestamp value to a _string_ representation when passing it across the wire; DST doesn't affect millis, but it does affect the toString() representation. So if we keep the value in millis when flattening and then reconstituting, it seems like we could work around the DST issue (and that right there would be a huge step in the right direction). Again, I'm not familiar enough w/ the internals of Postgres or even the JDBC implementation to tell you "this is how you should do it". I'm just saying that the way the current implementation flattens the timestamp via toString() seems fundamentally problematic to me. At the end of the day, I don't really care how we get there - we just need to be able to read/write timestamp data without having it munged... hopefully that's not too much to ask. Hope that helps, Christian
Hi Christian, I believe that the root of your difficulty is that you are asking the Timestamp object, which represents a moment in time, to represent something which, in your timezone, is not a valid moment in time. The alternative to the technique you describe is to use String objects within Java, which don't much care what they hold, rather than Timestamp objects with specific semantics which conflict with your values. I hope you have something that's working for you -- I know how painful conversions can be. -Kevin >>> Christian Cryder <c.s.cryder@gmail.com> 07/21/05 10:13 AM >>> On 7/20/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I am not sure however that Christian's basic complaint is solvable. > The problem here is very simple: the semantics of SQL TIMESTAMP WITHOUT > TIME ZONE do not match Java's Timestamp, no how, no way. For instance, > '2004-04-04 02:30' is an unconditionally valid timestamp-without-zone, > no matter what anybody's timezone setting is. However, if you try > to interpret it as local time in a US DST-observing zone, you have a > problem. You can never convert this value to timestamptz and back in > a DST-observing zone and not have it change ... which is basically > what Christian is hoping for. But there is *no* timestamptz value > that will decode as 02:30, because that's not a valid value for > timestamptz. Hi guys, Here's what we're looking for in a nutshell. The DB allows a date of '2004-04-04 02:30' in a timestamp column (eg. it may have gotten there via a manual insert from the sql query tool, etc. - However it got there, it's there; that's our starting point). Now, all we want is to be able to read that value via JDBC and then write it back via JDBC, and have the DB end up with same date that we started w/ at the beginning. And right now, it's _extremely_ difficult to accomplish that via the current Postgres driver implementation (we had to jump through hoops and create a custom PGTimestamp class, then wrap the postgres prepared statement, and even then, we still have to programatically turn off DST on the client...it works, but BLECH!) In my mind, that fact that it is so difficult to do such a simple thing should give us pause; it's a fundamental data integrity issue at the root. It's certainly causing us a lot of grief as we try to think about migrating to Postgres; and I'll bet we're not the only ones who see this as an issue. Please don't misunderstand - I realize the JDBC spec is vague, I understand that Sun's Timestamp implementation may have been poorly thought out. But at the end of the day, we have a timestamp value in Postgres - we need to be able to read/write that value without having it changed on us. We are currently using another DB who's JDBC implementation doesn't have this problem. We currently have lots and lots of code using PreparedStatements (so switching to Statements to work around this is not very realistic). The idea of having to turn off DST on our severs in order to make this work is also not a viable option (there are other things running on these boxes, where the time does matter). My point in all this is that regardless of who's to blame here, what we really need is a workable solution. What I was trying to say yesterday is that I think a big part of the problem hinges on the decision to convert the underlying timestamp value to a _string_ representation when passing it across the wire; DST doesn't affect millis, but it does affect the toString() representation. So if we keep the value in millis when flattening and then reconstituting, it seems like we could work around the DST issue (and that right there would be a huge step in the right direction). Again, I'm not familiar enough w/ the internals of Postgres or even the JDBC implementation to tell you "this is how you should do it". I'm just saying that the way the current implementation flattens the timestamp via toString() seems fundamentally problematic to me. At the end of the day, I don't really care how we get there - we just need to be able to read/write timestamp data without having it munged... hopefully that's not too much to ask. Hope that helps, Christian ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
The only instance variable in the java.sql.Timestamp class is a Java long primitive, which is the offset of the moment from 1970-01-01 00:00:00.0 GMT. There is absolutely no other information carried in an instance of this class. The meaining of a Timestamp object is clear and unambiguous. By default, rendering a Timestamp to a String is done using the default time zone for the JVM. This code snippet: Timestamp ts = new Timestamp(0L); System.out.println(ts); renders this on my console: 1969-12-31 18:00:00.0 because I'm in the U.S. Central time zone. I could use a specific Timezone object to render this as a string appropriate to any time zone of my choice. The questions would seem to be: (1) When storing a Timestamp object to a database column which is defined as a timestamp with a time zone, what time zone should be used? Regardless of the choice, the value in the column must represent the same moment as the original Timestamp object. It seems clear that some methods allow you to specify a Calendar object for the sole purpose of specifying the time zone, and that in the absence of that, the default time zone of the JVM should be used. (2) When storing a Timestamp object to a database column which is defined as a timestamp without a time zone, which time zone's local representation of the Timestamp object should be used to convert the Timestamp object from a moment to whatever the heck a timestamp without a time zone is? The rub is that the semantics of such a database column are not obvious. Does it represent the moment corresponding to the given year, month, day, hour, minute, second, etc. in the server's time zone, the client's time zone, GMT, or something else? There are use cases where each of these make sense, although the primary reason for HAVING a timestamp without a timezone, would seem to be so that the actual moment would be different for different clients. (For example, a database used in many time zones, which contained columns to configure when late-night maintenance activities on client machines should occur.) It seems to me that specifying a time zone on database writes in this case should cause the database representation of the moment represented by the Timestamp object to be the date and time of that moment in the specified time zone. I can't think of anything in the ANSI SQL or JDBC specifications or javadocs which provides any direction on what a timestamp without a time zone represents; however, there are two compelling reasons to use the time zone of the client: (a) The only reasonable use cases I can see for storing a timestamp which does NOT represent a moment in time involve interpreting it as different moments based on the client time zone. This means that software the is right and proper would want to use the local timezone, at least for retrieval, and we all like symmetry, don't we? (b) The most common reason for using a timestamp without a timezone is certainly that people are assuming that the whole system is based around a single timezone which they never explicitly specify, and they don't want to bother with it. In this case, the client and server time zones should match (or it was a very bad choice for data type). If the time zones DON'T match, we're dealing with a fundamentally screwed up situation at that point -- the schema is fundamentally inappropriate for the runtime environment. It seems to me that allowing the client side to specify a time zone when presenting the Timestamp to the database is the reasonable way to go, with the default being the default time zone for the JVM. The solution to Christian's problem seems to me to be that he should either get the timestamps from his source as String objects (using a database server side function to convert them to the desired character-based representation), and generate the values as literals in loading the new database, OR he should ensure that his client and sever use the same time zone and that the Timestamp objects actually represent the moments he cares about. Either should work. -Kevin
On 21-Jul-05, at 1:07 PM, Kevin Grittner wrote: > The only instance variable in the java.sql.Timestamp class is a Java > long primitive, which is the offset of the moment from 1970-01-01 > 00:00:00.0 GMT. There is absolutely no other information carried > in an > instance of this class. The meaining of a Timestamp object is > clear and > unambiguous. By default, rendering a Timestamp to a String is done > using the default time zone for the JVM. This code snippet: > > Timestamp ts = new Timestamp(0L); > System.out.println(ts); > > renders this on my console: > > 1969-12-31 18:00:00.0 > > because I'm in the U.S. Central time zone. I could use a specific > Timezone object to render this as a string appropriate to any time > zone > of my choice. The questions would seem to be: > > (1) When storing a Timestamp object to a database column which is > defined as a timestamp with a time zone, what time zone should be > used? > Regardless of the choice, the value in the column must represent the > same moment as the original Timestamp object. It seems clear that > some > methods allow you to specify a Calendar object for the sole purpose of > specifying the time zone, and that in the absence of that, the default > time zone of the JVM should be used. > According to the JDBC API tutorial the time zone of the server > (2) When storing a Timestamp object to a database column which is > defined as a timestamp without a time zone, which time zone's local > representation of the Timestamp object should be used to convert the > Timestamp object from a moment to whatever the heck a timestamp > without > a time zone is? The rub is that the semantics of such a database > column > are not obvious. Does it represent the moment corresponding to the > given year, month, day, hour, minute, second, etc. in the server's > time > zone, the client's time zone, GMT, or something else? There are use > cases where each of these make sense, although the primary reason for > HAVING a timestamp without a timezone, would seem to be so that the > actual moment would be different for different clients. (For > example, a > database used in many time zones, which contained columns to configure > when late-night maintenance activities on client machines should > occur.) > It seems to me that specifying a time zone on database writes in this > case should cause the database representation of the moment > represented > by the Timestamp object to be the date and time of that moment in the > specified time zone. > > I can't think of anything in the ANSI SQL or JDBC specifications or > javadocs which provides any direction on what a timestamp without a > time > zone represents; however, there are two compelling reasons to use the > time zone of the client: > > (a) The only reasonable use cases I can see for storing a timestamp > which does NOT represent a moment in time involve interpreting it as > different moments based on the client time zone. This means that > software the is right and proper would want to use the local timezone, > at least for retrieval, and we all like symmetry, don't we? > > (b) The most common reason for using a timestamp without a > timezone is > certainly that people are assuming that the whole system is based > around > a single timezone which they never explicitly specify, and they don't > want to bother with it. In this case, the client and server time > zones > should match (or it was a very bad choice for data type). If the time > zones DON'T match, we're dealing with a fundamentally screwed up > situation at that point -- the schema is fundamentally > inappropriate for > the runtime environment. It seems to me that allowing the client side > to specify a time zone when presenting the Timestamp to the > database is > the reasonable way to go, with the default being the default time zone > for the JVM. > > The solution to Christian's problem seems to me to be that he should > either get the timestamps from his source as String objects (using a > database server side function to convert them to the desired > character-based representation), and generate the values as > literals in > loading the new database, > > OR > > he should ensure that his client and sever use the same time zone and > that the Timestamp objects actually represent the moments he cares > about. > > Either should work. > > -Kevin > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > >
Could you post the URL so that I can find the reference without searching the whole trail? (My apologies if I missed this in an earlier message -- there have been so many on this topic that I sometimes skimmed.) That would seem to conflict with information from the URL below, from which I will paste a quote. http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/mapping.html Methods added in the JDBC 2.0 core API make it possible for the driver to take a specified time zone into account when calculating a date, time, or timestamp. The time zone information is included in a java.util.Calendar object that is passed to new versions of the methods for getting and setting Date, Time, and Timestamp values. When no time zone is specified, the driver uses the time zone of the virtual machine running the application when it calculates a date, time, or timestamp. >>> Dave Cramer <pg@fastcrypt.com> 07/21/05 3:52 PM >>> On 21-Jul-05, at 1:07 PM, Kevin Grittner wrote: > (1) When storing a Timestamp object to a database column which is > defined as a timestamp with a time zone, what time zone should be > used? > Regardless of the choice, the value in the column must represent the > same moment as the original Timestamp object. It seems clear that > some > methods allow you to specify a Calendar object for the sole purpose of > specifying the time zone, and that in the absence of that, the default > time zone of the JVM should be used. > According to the JDBC API tutorial the time zone of the server
Sorry, I have the book, no url. I'll see if I can resolve this though Dave On 21-Jul-05, at 5:09 PM, Kevin Grittner wrote: > Could you post the URL so that I can find the reference without > searching the whole trail? (My apologies if I missed this in an > earlier > message -- there have been so many on this topic that I sometimes > skimmed.) > > That would seem to conflict with information from the URL below, from > which I will paste a quote. > > http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/mapping.html > > Methods added in the JDBC 2.0 core API make it possible for the driver > to take a specified time zone into account when calculating a date, > time, or timestamp. The time zone information is included in a > java.util.Calendar object that is passed to new versions of the > methods > for getting and setting Date, Time, and Timestamp values. When no time > zone is specified, the driver uses the time zone of the virtual > machine > running the application when it calculates a date, time, or timestamp. > > > >>>> Dave Cramer <pg@fastcrypt.com> 07/21/05 3:52 PM >>> >>>> > > On 21-Jul-05, at 1:07 PM, Kevin Grittner wrote: > > >> (1) When storing a Timestamp object to a database column which is >> defined as a timestamp with a time zone, what time zone should be >> used? >> Regardless of the choice, the value in the column must represent the >> same moment as the original Timestamp object. It seems clear that >> some >> methods allow you to specify a Calendar object for the sole >> purpose of >> specifying the time zone, and that in the absence of that, the >> default >> time zone of the JVM should be used. >> >> > According to the JDBC API tutorial the time zone of the server > > >
Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> writes: > >>That's correct. A Timestamp is essentially a wrapper around >>milliseconds-since-epoch. > > > OK --- that is also our interpretation of TIMESTAMP WITH TIME ZONE > (at the moment anyway --- there's some agitation for putting an > explicit zone identifier in too, because it looks like the SQL spec > requires that). After some thought, an explicit zone identifier would solve the java-side problems if it also meant that the timestamptz to timestamp cast was done using that timezone. i.e. currently the core of the problem is this behaviour: template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp; timestamp --------------------- 2005-01-01 18:00:00 (1 row) This is not the most obvious result until you realize that timestamptz loses timezone info: template1=# select '2005-01-01 15:00:00 +1000'::timestamptz; timestamptz ------------------------ 2005-01-01 18:00:00+13 (1 row) I'd like to see the first case above produce 15:00:00, which seems doable if timestamptz is storing a timezone offset as well as a milliseconds-per-epoch. If we had that behaviour, then we just always pass down a timestamptz parameter with an appropriate timezone, and any implicit cast to timestamp "just works". This is similar in effect to using UNKNOWN in some ways since with UNKNOWN, if it really is a timestamp that is needed, the timestamp parser works on the input directly and throws away the timezone rather than doing the timezone juggling that timestamptz -> timestamp does: template1=# select '2005-01-01 15:00:00 +1000'::timestamp; timestamp --------------------- 2005-01-01 15:00:00 (1 row) -O
Oliver Jowett <oliver@opencloud.com> writes: > i.e. currently the core of the problem is this behaviour: > template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp; > timestamp > --------------------- > 2005-01-01 18:00:00 > (1 row) Well, the current interpretation is that timestamptz -> timestamp produces a timestamp representing what the timestamptz equates to in your current TimeZone. I do not foresee changing that behavior when/if we add explicit TZ info to timestamptz --- it would break just about every existing app that uses this conversion. In any case, this wouldn't solve Christian's complaint, because the root of his problem is that the value ever goes through timestamptz at all. That is necessarily going to "munge" values that don't correspond to legal local times in whatever zone you are using. The more I think about this, the more I think that the correct solution must include having the driver set TimeZone = UTC (and complain if the app tries to change it). Then you can specify parameter types as either timestamp or timestamptz, it doesn't really matter, because conversions between them on the server side will be no-ops. When you convert a Java Timestamp to send to the server, you always convert it using a UTC Calendar object. I'm not sure if the setTimestamp variants with a Calendar are special in this regime; arguably you should ignore the supplied Calendar, on the principle that you know what the Timestamp is supposed to mean. regards, tom lane
Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> writes: > >>i.e. currently the core of the problem is this behaviour: > > >>template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp; >> timestamp >>--------------------- >> 2005-01-01 18:00:00 >>(1 row) > > > Well, the current interpretation is that timestamptz -> timestamp > produces a timestamp representing what the timestamptz equates to in > your current TimeZone. I do not foresee changing that behavior when/if > we add explicit TZ info to timestamptz --- it would break just about > every existing app that uses this conversion. I can understand backwards compatibility issues, but it doesn't make my example above any more obvious :/ > The more I think about this, the more I think that the correct solution > must include having the driver set TimeZone = UTC (and complain if the > app tries to change it). Then you can specify parameter types as either > timestamp or timestamptz, it doesn't really matter, because conversions > between them on the server side will be no-ops. When you convert a Java > Timestamp to send to the server, you always convert it using a UTC > Calendar object. I'm not sure if the setTimestamp variants with a > Calendar are special in this regime; arguably you should ignore the > supplied Calendar, on the principle that you know what the Timestamp is > supposed to mean. I don't think this works, as we need to support the Calendar variants. Specifically, given this code: // 2005-01-01 05:00:00 UTC Timestamp now = new Timestamp(1104555600*1000L); Calender localCalendar = Calendar.getInstance(new Locale("en","NZ")); ps.setTimestamp(1, now, localCalendar); I would expect it to end up setting a timestamptz value of '2005-01-01 05:00:00 +0000' (or an equivalent instant as represented in some other timezone), or a timestamp of '2005-01-01 18:00:00' (as NZ is +1300 in January). I think this is the correct interpretation given what the javadoc says about setTimestamp() using the provided calendar to construct an appropriate SQL TIMESTAMP value. The JDBC spec may be lousy but I don't think they added the Calendar variants with the intention of having drivers completely ignore that parameter.. I don't see how we can handle this even with TimeZone = UTC: If we pass '2005-01-01 05:00:00 +0000' as timestamptz then it is wrong if implicitly cast to timestamp; if we pass it as timestamp then it's just the wrong value immediately. If we pass '2005-01-01 18:00:00 +1300' as timestamptz then it still doesn't cast to the correct timestamp value; if we pass it as timestamp then it is the right timestamp value, but is wrong if it is then cast to timestamptz. Passing '2005-01-01 18:00:00 +1300' as an unknown type will work for both timestamp and timestamptz as there's no implicit casting involved, but we run the risk of getting the type wrong or not being able to determine a type at all. Conversely, this is effectively what the V2 protocol path is doing anyway. If we can't change the casting behaviour, and TimeZone hackery doesn't work, and we want to support both timestamp and timestamptz via the standard interfaces, then it seems like passing the parameters as UNKNOWN is the only practical option :( -O
Oliver Jowett wrote: > Calender localCalendar = Calendar.getInstance(new Locale("en","NZ")); Sorry, getting locale and timezone confused, I meant something like this: Calendar localCalendar = Calendar.getInstance(TimeZone.getInstance("NZST")); -O
I've hacked my local version to track the servers time zone and use the calendar appropriate to the servers time zone. This would allow the user to set the driver, and server time zone to UTC as suggested below. One issue I have is that the server returns some time zones in lowercase. ie EST -> est EST5EDT -> est5edt java doesn't understand these. Why is the server changing them to lower case? Dave On 21-Jul-05, at 9:57 PM, Oliver Jowett wrote: > Tom Lane wrote: > >> Oliver Jowett <oliver@opencloud.com> writes: >> >> >>> i.e. currently the core of the problem is this behaviour: >>> >> >> >> >>> template1=# select '2005-01-01 15:00:00 >>> +1000'::timestamptz::timestamp; >>> timestamp >>> --------------------- >>> 2005-01-01 18:00:00 >>> (1 row) >>> >> >> >> Well, the current interpretation is that timestamptz -> timestamp >> produces a timestamp representing what the timestamptz equates to in >> your current TimeZone. I do not foresee changing that behavior >> when/if >> we add explicit TZ info to timestamptz --- it would break just about >> every existing app that uses this conversion. >> > > I can understand backwards compatibility issues, but it doesn't > make my > example above any more obvious :/ > > >> The more I think about this, the more I think that the correct >> solution >> must include having the driver set TimeZone = UTC (and complain if >> the >> app tries to change it). Then you can specify parameter types as >> either >> timestamp or timestamptz, it doesn't really matter, because >> conversions >> between them on the server side will be no-ops. When you convert >> a Java >> Timestamp to send to the server, you always convert it using a UTC >> Calendar object. I'm not sure if the setTimestamp variants with a >> Calendar are special in this regime; arguably you should ignore the >> supplied Calendar, on the principle that you know what the >> Timestamp is >> supposed to mean. >> > > I don't think this works, as we need to support the Calendar variants. > Specifically, given this code: > > // 2005-01-01 05:00:00 UTC > Timestamp now = new Timestamp(1104555600*1000L); > Calender localCalendar = Calendar.getInstance(new Locale > ("en","NZ")); > ps.setTimestamp(1, now, localCalendar); > > I would expect it to end up setting a timestamptz value of '2005-01-01 > 05:00:00 +0000' (or an equivalent instant as represented in some other > timezone), or a timestamp of '2005-01-01 18:00:00' (as NZ is +1300 in > January). I think this is the correct interpretation given what the > javadoc says about setTimestamp() using the provided calendar to > construct an appropriate SQL TIMESTAMP value. The JDBC spec may be > lousy > but I don't think they added the Calendar variants with the > intention of > having drivers completely ignore that parameter.. > > I don't see how we can handle this even with TimeZone = UTC: > > If we pass '2005-01-01 05:00:00 +0000' as timestamptz then it is wrong > if implicitly cast to timestamp; if we pass it as timestamp then it's > just the wrong value immediately. > > If we pass '2005-01-01 18:00:00 +1300' as timestamptz then it still > doesn't cast to the correct timestamp value; if we pass it as > timestamp > then it is the right timestamp value, but is wrong if it is then > cast to > timestamptz. > > Passing '2005-01-01 18:00:00 +1300' as an unknown type will work for > both timestamp and timestamptz as there's no implicit casting > involved, > but we run the risk of getting the type wrong or not being able to > determine a type at all. Conversely, this is effectively what the V2 > protocol path is doing anyway. > > If we can't change the casting behaviour, and TimeZone hackery doesn't > work, and we want to support both timestamp and timestamptz via the > standard interfaces, then it seems like passing the parameters as > UNKNOWN is the only practical option :( > > -O > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > >
I looked for this in the trail, and couldn't find it. So here it is verbatim setTimestamp with a calendar Sets parameter number parameterIndex to x. When the DBMS does not store time zone information, the driver will use cal to construct a JDBC Timestamp value which it then sends to the database. With a Calendar object the driver can calculate the Timestamp object taking into account a custom time zone. If no Calendar object is specified, the driver uses the time zone of the Virtual Machine that is running the application. So my interpretation is. If we are storing a timestamptz then ignore cal and use the server time zone If we are storing a timestamp then use cal to calculate the timestamp; if cal is null then use the JVM's time zone. getTimestamp is a little less clear When a getTimestamp method is called, the driver converts a JDBC TIMESTAMP instance, which is generally a string, to a java.sql.Timestamp object, which is a milliseconds value. In order to calculate the milliseconds, the driver takes into account the time zone, information a DBMS may or may not store. Because a Timestamp object itself has no way to keep track of time zone information, the driver relies on a java.util.Calendar object to get this information. If no Calendar object is supplied the the driver will use the default Calendar, whose time zone is that of the JVM that is running the application. If the DBMS does provide time zone information, the driver will simply use that and ignore a Calendar object that may be passed to it. Therefore to be portable, an application should supply a Calendar object to a getTimestamp method thereby not relying on the DBMS to supply a time zone but still being able to use the time zone supplied by a DBMS when one is available. I'm unclear whether "provides time zone information" means the server time zone, or time zone returned in the data. That aside: If the server provides time zone info, use that to create the timestamp, regardless of calendar supplied if the server does not provide time zone info, use calendar supplied, if that is null or not supplied then use JVM calendar. Dave On 21-Jul-05, at 5:09 PM, Kevin Grittner wrote: > Could you post the URL so that I can find the reference without > searching the whole trail? (My apologies if I missed this in an > earlier > message -- there have been so many on this topic that I sometimes > skimmed.) > > That would seem to conflict with information from the URL below, from > which I will paste a quote. > > http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/mapping.html > > Methods added in the JDBC 2.0 core API make it possible for the driver > to take a specified time zone into account when calculating a date, > time, or timestamp. The time zone information is included in a > java.util.Calendar object that is passed to new versions of the > methods > for getting and setting Date, Time, and Timestamp values. When no time > zone is specified, the driver uses the time zone of the virtual > machine > running the application when it calculates a date, time, or timestamp. > > > >>>> Dave Cramer <pg@fastcrypt.com> 07/21/05 3:52 PM >>> >>>> > > On 21-Jul-05, at 1:07 PM, Kevin Grittner wrote: > > >> (1) When storing a Timestamp object to a database column which is >> defined as a timestamp with a time zone, what time zone should be >> used? >> Regardless of the choice, the value in the column must represent the >> same moment as the original Timestamp object. It seems clear that >> some >> methods allow you to specify a Calendar object for the sole >> purpose of >> specifying the time zone, and that in the absence of that, the >> default >> time zone of the JVM should be used. >> >> > According to the JDBC API tutorial the time zone of the server > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >
On 21-Jul-05, at 8:54 PM, Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> writes: > >> i.e. currently the core of the problem is this behaviour: >> > > >> template1=# select '2005-01-01 15:00:00 >> +1000'::timestamptz::timestamp; >> timestamp >> --------------------- >> 2005-01-01 18:00:00 >> (1 row) >> > > Well, the current interpretation is that timestamptz -> timestamp > produces a timestamp representing what the timestamptz equates to in > your current TimeZone. I do not foresee changing that behavior > when/if > we add explicit TZ info to timestamptz --- it would break just about > every existing app that uses this conversion. > > In any case, this wouldn't solve Christian's complaint, because the > root > of his problem is that the value ever goes through timestamptz at all. > That is necessarily going to "munge" values that don't correspond to > legal local times in whatever zone you are using. > > The more I think about this, the more I think that the correct > solution > must include having the driver set TimeZone = UTC (and complain if the > app tries to change it). This really isn't an option. We can't impose limits on the application like this. > Then you can specify parameter types as either > timestamp or timestamptz, it doesn't really matter, because > conversions > between them on the server side will be no-ops. When you convert a > Java > Timestamp to send to the server, you always convert it using a UTC > Calendar object. I'm not sure if the setTimestamp variants with a > Calendar are special in this regime; arguably you should ignore the > supplied Calendar, on the principle that you know what the > Timestamp is > supposed to mean. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >
Dave Cramer <pg@fastcrypt.com> writes: > One issue I have is that the server returns some time zones in > lowercase. > ie EST -> est > EST5EDT -> est5edt I think that in some contexts the case you will get back is what you give in the SET command. So do set timezone = 'EST5EDT'; not set timezone = EST5EDT; (the latter is downcased as if it were an identifier). regards, tom lane
Dave Cramer <pg@fastcrypt.com> writes: > On 21-Jul-05, at 8:54 PM, Tom Lane wrote: >> The more I think about this, the more I think that the correct >> solution >> must include having the driver set TimeZone = UTC (and complain if the >> app tries to change it). > This really isn't an option. We can't impose limits on the > application like this. Why not? Why should the application care what the server's timezone is? Especially in a context where it's up to the driver to manage all these conversion issues... regards, tom lane
On 22-Jul-05, at 9:42 AM, Tom Lane wrote: > Dave Cramer <pg@fastcrypt.com> writes: > >> One issue I have is that the server returns some time zones in >> lowercase. >> > > >> ie EST -> est >> EST5EDT -> est5edt >> > > I think that in some contexts the case you will get back is what you > give in the SET command. So do > set timezone = 'EST5EDT'; > not > set timezone = EST5EDT; > (the latter is downcased as if it were an identifier). > Ick .... this makes things tougher ... The user won't necessarily quote it if they change it. I guess if we restrict changing it, as per your other suggestion, then this goes away. > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > >
Argh! I've found some comments on this thread confusing, and I've discovered the source of this confusion -- I was thinking that a column defined as type "timestamp with time zone" would store a time zone with the timestamp. (Really, I did read the documentation on data types, but the discussion of how this data type is implemented slid right out of my head.) I will have to review everything with the actual implementation in mind; I now see why this is more complicated than I thought. Could someone confirm that a timestamp column in a PostgreSQL database never contains time zone data, regardless of whether or not the "with time zone" clause was used in its definition. It seems to me that, if you ignore internals of how the data is stored (which an application programmer never need see), the only difference between "timestamp with time zone" and "timestamp without time zone" is whether a time zone specified in a literal will (a) be used to determine what moment the timestamp represents or (b) be ignored in favor of using the server's time zone to determine what moment the timestamp represents. In either case, the value returned when querying the column will be based on the server's time zone. Do I have that right? -Kevin
On 22-Jul-05, at 12:30 PM, Kevin Grittner wrote: > Argh! I've found some comments on this thread confusing, and I've > discovered the source of this confusion -- I was thinking that a > column > defined as type "timestamp with time zone" would store a time zone > with > the timestamp. (Really, I did read the documentation on data > types, but > the discussion of how this data type is implemented slid right out > of my > head.) I will have to review everything with the actual > implementation > in mind; I now see why this is more complicated than I thought. > > Could someone confirm that a timestamp column in a PostgreSQL database > never contains time zone data, regardless of whether or not the "with > time zone" clause was used in its definition. yes, that is correct. > It seems to me that, if > you ignore internals of how the data is stored (which an application > programmer never need see), the only difference between "timestamp > with > time zone" and "timestamp without time zone" is whether a time zone > specified in a literal will (a) be used to determine what moment the > timestamp represents or (b) be ignored in favor of using the server's > time zone to determine what moment the timestamp represents. In > either > case, the value returned when querying the column will be based on the > server's time zone. Do I have that right? That is my understanding yhest. > > -Kevin > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > Could someone confirm that a timestamp column in a PostgreSQL database > never contains time zone data, regardless of whether or not the "with > time zone" clause was used in its definition. It seems to me that, if > you ignore internals of how the data is stored (which an application > programmer never need see), the only difference between "timestamp with > time zone" and "timestamp without time zone" is whether a time zone > specified in a literal will (a) be used to determine what moment the > timestamp represents or (b) be ignored in favor of using the server's > time zone to determine what moment the timestamp represents. In either > case, the value returned when querying the column will be based on the > server's time zone. Do I have that right? No, not really. Timestamp with time zone is effectively equivalent to Java's Timestamp (as the latter was explained to me in this thread, anyway): all it stores is an absolute time instant referenced to UTC. On input, any time zone specified (or implied --- the default is to use the TimeZone setting) is handled by rotating the value to UTC. On output, the server rotates the time instant into the current TimeZone for display. So setting TimeZone is equivalent to using a Calendar for conversion of a Java Timestamp. There has been talk of changing this behavior for improved SQL spec compatibility, but that's how it is at the moment. Timestamp without time zone is a fundamentally different animal, because there simply is no concept of time zones at all. It will store any y/m/d/h/m/s values that are legal per the rules of the Gregorian calendar. Changing TimeZone does not affect either input interpretation or display of a previously stored value, where TimeZone does affect both input and display behavior of timestamptz. I think it's really a mistake to consider timestamp without time zone as representing any absolutely identifiable time instant at all. Unfortunately, we are probably going to have to deal with complaints from people who have used it in ways that amount to assuming it does represent such an instant ... regards, tom lane
I'm not trying to be argumentative here, but I'm afraid I'm missing something important. In what way is the behavior you describe functionally different to someone using the database from what I described? What differences in _behavior_ are exposed to someone _using_ the database, other than ignoring time zone information for the "timestamp without time zone" values? Thanks for any clarity you can bring me on this. -Kevin >>> Tom Lane <tgl@sss.pgh.pa.us> 07/22/05 12:03 PM >>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > Could someone confirm that a timestamp column in a PostgreSQL database > never contains time zone data, regardless of whether or not the "with > time zone" clause was used in its definition. It seems to me that, if > you ignore internals of how the data is stored (which an application > programmer never need see), the only difference between "timestamp with > time zone" and "timestamp without time zone" is whether a time zone > specified in a literal will (a) be used to determine what moment the > timestamp represents or (b) be ignored in favor of using the server's > time zone to determine what moment the timestamp represents. In either > case, the value returned when querying the column will be based on the > server's time zone. Do I have that right? No, not really. Timestamp with time zone is effectively equivalent to Java's Timestamp (as the latter was explained to me in this thread, anyway): all it stores is an absolute time instant referenced to UTC. On input, any time zone specified (or implied --- the default is to use the TimeZone setting) is handled by rotating the value to UTC. On output, the server rotates the time instant into the current TimeZone for display. So setting TimeZone is equivalent to using a Calendar for conversion of a Java Timestamp. There has been talk of changing this behavior for improved SQL spec compatibility, but that's how it is at the moment. Timestamp without time zone is a fundamentally different animal, because there simply is no concept of time zones at all. It will store any y/m/d/h/m/s values that are legal per the rules of the Gregorian calendar. Changing TimeZone does not affect either input interpretation or display of a previously stored value, where TimeZone does affect both input and display behavior of timestamptz. I think it's really a mistake to consider timestamp without time zone as representing any absolutely identifiable time instant at all. Unfortunately, we are probably going to have to deal with complaints from people who have used it in ways that amount to assuming it does represent such an instant ... regards, tom lane
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > What differences in _behavior_ are exposed to someone > _using_ the database, other than ignoring time zone information for the > "timestamp without time zone" values? Well, other than the point that changing TimeZone changes the displayed values of timestamptz but not timestamp, there's the fact that timestamptz will reject (or change, actually) some values that timestamp will consider valid. For instance '2005-04-03 02:30' is valid to timestamp, but in any US timezone it will come out as something different in timestamptz. regression=# show timezone; TimeZone ---------- EST5EDT (1 row) regression=# select '2005-04-03 02:30'::timestamp; timestamp --------------------- 2005-04-03 02:30:00 (1 row) regression=# select '2005-04-03 02:30'::timestamptz; timestamptz ------------------------ 2005-04-03 03:30:00-04 (1 row) regards, tom lane
Dave Cramer wrote: > I looked for this in the trail, and couldn't find it. > So here it is verbatim > > setTimestamp with a calendar > > Sets parameter number parameterIndex to x. When the DBMS does not store > time zone information, the driver will use cal to construct a JDBC > Timestamp value which it then sends to the database. With a Calendar > object the driver can calculate the Timestamp object taking into > account a custom time zone. If no Calendar object is specified, the > driver uses the time zone of the Virtual Machine that is running the > application. > > > So my interpretation is. > > If we are storing a timestamptz then ignore cal and use the server time > zone > If we are storing a timestamp then use cal to calculate the timestamp; > if cal is null then use the JVM's time zone. Couple of things.. a) How do we know what type we are storing without invoking UNKNOWN again? b) The server doesn't actually store timezone information with a timestamptz (this confused me initially, but Tom clarified), so shouldn't we be doing what the book says and *using* the calendar when we store to a timestamptz? (it's just a representation issue, it doesn't actually affect the value stored in the end.. unless you plug it into a text column or something..) > That aside: > > If the server provides time zone info, use that to create the > timestamp, regardless of calendar supplied > if the server does not provide time zone info, use calendar supplied, > if that is null or not supplied then use JVM calendar. That makes sense -- basically it means that if we are reading a timestamptz we construct the Timestamp directly to represent that instant, ignoring the calendar supplied. If we are reading a timestamp, then we turn it into a Timestamp using the provided or JVM-default calendar. -O
Tom Lane wrote: > Vadim Nasardinov <vadimn@redhat.com> writes: > >>If 8.0 comes with its own copy of a recent zic database, then, in >>theory, it should be able to handle any timezone known to Java. (The >>opposite is not necessarily true, as Java's timezone info seems to be >>missing a lot of recent changes.) Does that sound like a correct >>statement to you or did I forget some important qualifiers? > > > Yeah, it seems that we are just open to version skew issues between > different copies of the zic database ... which changes enough that > that's a nontrivial risk, but I fear we are unlikely to do better. I noticed in passing that Java and postgres have different ideas about what (for example) "GMT+10" as a timezone name means. Java considers that to be east of GMT, postgres thinks it is west. -O