Thread: Timestamp Summary
Hey folks,
I just wanted to take a few moments and summarize where we are in our
Timestamp woes issue. I'm also going to post a piece of code that
illustrates some of the problems we've experienced, and then try and
explain the conclusions we've arrived at. Hopefully this well help
others in the future.
First of all, the code snippet to easily duplicate the problem (see
the bottom of this email). What this code does is create a table Foo,
and then it inserts 4 rows using a SQL Statement. It then turns around
and reads those values via JDBC, and then simply re-inserts the values
back into the DB. As the example illustrates, the data written is NOT
the same as the data read (you can see this by querying the table
through the sql console after you've run the example).
The key point to note here is that the 4 dates being inserted
originally fall immediately around and in the daylight savings cutover
(2-3 AM, 1st Sunday of April). So t1 is before daylight savings, t2 is
during the rollover (and thus technically not a valid daylight savings
time), t3 and t4 are after the rollover.
With DST turned on on the client, here's the results in the table...
----------------------------------------------------
101;"2005-04-03 00:06:02"
102;"2005-04-03 02:29:43"
103;"2005-04-03 03:29:43"
104;"2005-04-03 04:35:17"
105;"2005-04-03 00:06:02"
106;"2005-04-03 03:29:43"
107;"2005-04-03 03:29:43"
108;"2005-04-03 04:35:17"
This looks pretty good except that the original t2 (02:29) got munged
to 03:29 in the DB..
With DST programatically turned off on the client, the dates display
properly in the client output (no munging), but they still get munged
on the server side of things (because there, DST is still turned on) -
and there, everything above the cutoff gets adjusted.
-----------------------------------------------------
125;"2005-04-03 00:06:02"
126;"2005-04-03 02:29:43"
127;"2005-04-03 03:29:43"
128;"2005-04-03 04:35:17"
129;"2005-04-03 00:06:02"
130;"2005-04-03 03:29:43"
131;"2005-04-03 04:29:43"
132;"2005-04-03 05:35:17"
So if we turn off DST on both the client -AND- the server (for me, the
server part happens by issuing a "set timezone='etc/gmt+7'" before I
use the connection), we finally get what we're looking for - write,
read, write, w/ proper values on both display and in the db when we're
all done.
-----------------------------------------------------
133;"2005-04-03 00:06:02"
134;"2005-04-03 02:29:43"
135;"2005-04-03 03:29:43"
136;"2005-04-03 04:35:17"
137;"2005-04-03 00:06:02"
138;"2005-04-03 02:29:43"
139;"2005-04-03 03:29:43"
140;"2005-04-03 04:35:17"
In other words, nothing got munged.
At this point, I'd like to submit a piece of code that will ensure
that the server is operating in the same time zone as the client
connection, taking DST settings into consideration as well (Dave
should understand where this would go as he was playing around with
this type of thing when helping me debug last week):
//in order to avoid munging dates on insert, we need to set
the server to the same timezone
//as the client for the duration of this connection. If
daylight savings is turned on, this is
//simple: just send the current timezone; if dst is turned
off, however, then we need to send the GMT
//equivalent (w/ no DST offset), which takes the form of
etc/gmt+X (where X is the number of hours)
TimeZone tz = TimeZone.getDefault();
int roff = tz.getRawOffset();
if (tz.useDaylightTime()) {
stmt.execute("set timezone='"+tz.getID().toLowerCase()+"'");
} else {
stmt.execute("set timezone='etc/gmt"+(roff<=0 ? "+" :
"")+(roff/-3600000)+"'");
}
This piece of code only needs to get executed when the connection is
first opened to the DB, and from that point on it will ensure that the
db is in functionally the same timezone as the server (taking DST
on/off into account as well). In other words, if my client is running
in MST w/ DST turned off, the server will be configured to etc/gmt+7,
and any dates I insert will be left untouched.
That make sense?
If you'd like to see an example of the strings generated for each
timezone, here's a snippet...
String zones[] = TimeZone.getAvailableIDs();
for (int i=0; i<zones.length; i++) {
TimeZone tz = TimeZone.getTimeZone(zones[i]);
int roff = tz.getRawOffset();
if (!tz.useDaylightTime()) {
System.out.println("server: 'etc/gmt"+(roff<=0 ? "+" :
"")+(roff/-3600000)+"' ... "+i+": "+tz);
} else {
System.out.println("server: '"+tz.getID().toLowerCase()+"' ...
"+i+": "+tz);
}
}
Ok, so here's the summary:
a) w/ DST turned on on the client, the client munges t2 forward to a
valid time and inserts into the db (so we read 02:29 and write 03:29)
b) w/ DST turned off on the client, the client does not munge t2, and
neither does the db because its been tweaked to match (so we read
02:29 and write 02:29)
So if I want to insert dates without munging, all I have to do is turn
DST off in the client (and the example below illustrates how to do
this for the current timezone).
I'd like to request that we apply this patch to the JDBC drivers
(again, Dave will know where to put it). This should not break any
existing code, and it will make it possible to ensure that client code
can insert dates without munging, and without forcing DST to be turned
off systemwide on the server - it basically allows us to drive the
setting on a per connection basis.
Any feedback?
Thanks,
Christian
(and thanks especially to Dave for helping me debug all this stuff
over the past 10 days)
-------------------------------------------------
Code snippet to duplicate problem
-------------------------------------------------
//see what time zone we are running in (and optionally turn off DST)
TimeZone curTz = TimeZone.getDefault();
TimeZone curTzNoDST = new SimpleTimeZone(curTz.getRawOffset(),
curTz.getID());
//uncomment the line below to see what happens w/ DST turned off
// TimeZone.setDefault(curTzNoDST); //this will turn off DST in
the local JVM
System.out.println("current tz:"+TimeZone.getDefault());
//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 (?)");
//drop, create the table
try {stmt.execute("TRUNCATE TABLE Foo");}
catch (SQLException e) {stmt.execute("CREATE TABLE Foo (UID
SERIAL, TrxTime timestamp without time zone NOT NULL);");}
//in order to avoid munging dates on insert, we need to set
the server to the same timezone
//as the client for the duration of this connection. If
daylight savings is turned on, this is
//simple: just send the current timezone; if dst is turned
off, however, then we need to send the GMT
//equivalent (w/ no DST offset), which takes the form of
etc/gmt+X (where X is the number of hours)
TimeZone tz = TimeZone.getDefault();
int roff = tz.getRawOffset();
if (tz.useDaylightTime()) {
stmt.execute("set timezone='"+tz.getID().toLowerCase()+"'");
} else {
stmt.execute("set timezone='etc/gmt"+(roff<=0 ? "+" :
"")+(roff/-3600000)+"'");
}
//now start with raw inserts (via statement) - these will
correspond to the 1st 3 rows of data in the db
System.out.println();
stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
00:06:02.0')");
stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
02:29:43.0')");
stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
03:29:43.0')");
stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
04:35:17.0')");
System.out.println("insert-1 t1: 2005-04-03 00:06:02.0
(millis: "+Timestamp.valueOf("2005-04-03 00:06:02.0").getTime()+")");
System.out.println("insert-1 t2: 2005-04-03 02:29:43.0
(millis: "+Timestamp.valueOf("2005-04-03 02:29:43.0").getTime()+")");
System.out.println("insert-1 t3: 2005-04-03 03:29:43.0
(millis: "+Timestamp.valueOf("2005-04-03 03:29:43.0").getTime()+")");
System.out.println("insert-1 t4: 2005-04-03 04:35:17.0
(millis: "+Timestamp.valueOf("2005-04-03 04:35:17.0").getTime()+")");
//now read the table, getting the data we just inserted (in
your output, you will notice the timestamps differ)
System.out.println();
ResultSet rs = stmt.executeQuery("SELECT * FROM Foo");
List rows = new ArrayList();
int cntr = 0;
while (rs.next()) {
Object uid = rs.getObject(1);
t = rs.getTimestamp(2);
System.out.println("result-1 t"+(++cntr)+": "+t+" (millis:
"+t.getTime()+")");
rows.add(new Object[] {uid, t});
}
rs.close();
//now iterate through the sample data and re-insert
System.out.println();
Iterator it = rows.iterator();
cntr = 0;
while (it.hasNext()) {
Object[] cols = (Object[]) it.next();
t = (Timestamp) cols[1];
pstmt.setTimestamp(1, t);
System.out.println("insert-2 t"+(++cntr)+": "+t+" (millis:
"+t.getTime()+")");
pstmt.executeUpdate();
}
//now read the values back out (here we'll get all 6 values
out; the last 3 correspond to 2nd set of inserts)
System.out.println();
rs = stmt.executeQuery("SELECT * FROM Foo");
cntr = 0;
while (rs.next()) {
t = rs.getTimestamp(2);
System.out.println("result-2 t"+(++cntr)+": "+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) {}
}
As someone who is interested in timestamp columns only to hold actual moments in time, I'm very uncomfortable with Christian's proposed "fix". We have a highly multithreaded environment dealing with multiple database servers. Having various threads all setting the default timezone for the JVM to something inaccurate based on connections to a variety of servers seems likely to break much more than it fixes. The primary "problem" being solved by this technique is that it is hard to record a timestamp representing a moment which doesn't exist any more than do the following: 2005-02-29 00:00:00.0 2005-10-35 00:00:00.0 2005-10-25 00:75:00.0 It is fine with me if moments that don't exist can't be stored in the database. Others have pointed out problems with storage and retrieval of valid Timestamp objects. Those seem to me to be the problems to address. I think that would go part of the way toward addressing Christian's problems; but, since you can't actually create a Timestamp object within a JVM set to the correct time zone to represent what he wants, his particular issue will always require munging the Java runtime environment, which is simply not an option in many situations. -Kevin
If this code only executes when the connection is established, then it
will break for any long-lived connection that lives across a change in
DST.
This could be fixed by moving this code from connection init to just
before we send any queries to the back end, sort of like:
if(defaultTz.useDaylightTime() != oldUseDaylightTime) {
runDSTCorrectionCode();
}
I don't know how cheap the useDaylightTime() call is, so I'm not sure
what the performance impact of doing this on every query would be like.
If it's bad, then maybe we'd need to program in some other way to know
when DST turns on/off.
On Mon, 2005-07-25 at 10:45 -0600, Christian Cryder wrote:
> Hey folks,
>
> I just wanted to take a few moments and summarize where we are in our
> Timestamp woes issue. I'm also going to post a piece of code that
> illustrates some of the problems we've experienced, and then try and
> explain the conclusions we've arrived at. Hopefully this well help
> others in the future.
>
> First of all, the code snippet to easily duplicate the problem (see
> the bottom of this email). What this code does is create a table Foo,
> and then it inserts 4 rows using a SQL Statement. It then turns around
> and reads those values via JDBC, and then simply re-inserts the values
> back into the DB. As the example illustrates, the data written is NOT
> the same as the data read (you can see this by querying the table
> through the sql console after you've run the example).
>
> The key point to note here is that the 4 dates being inserted
> originally fall immediately around and in the daylight savings cutover
> (2-3 AM, 1st Sunday of April). So t1 is before daylight savings, t2 is
> during the rollover (and thus technically not a valid daylight savings
> time), t3 and t4 are after the rollover.
>
> With DST turned on on the client, here's the results in the table...
> ----------------------------------------------------
> 101;"2005-04-03 00:06:02"
> 102;"2005-04-03 02:29:43"
> 103;"2005-04-03 03:29:43"
> 104;"2005-04-03 04:35:17"
>
> 105;"2005-04-03 00:06:02"
> 106;"2005-04-03 03:29:43"
> 107;"2005-04-03 03:29:43"
> 108;"2005-04-03 04:35:17"
>
> This looks pretty good except that the original t2 (02:29) got munged
> to 03:29 in the DB..
>
> With DST programatically turned off on the client, the dates display
> properly in the client output (no munging), but they still get munged
> on the server side of things (because there, DST is still turned on) -
> and there, everything above the cutoff gets adjusted.
> -----------------------------------------------------
> 125;"2005-04-03 00:06:02"
> 126;"2005-04-03 02:29:43"
> 127;"2005-04-03 03:29:43"
> 128;"2005-04-03 04:35:17"
>
> 129;"2005-04-03 00:06:02"
> 130;"2005-04-03 03:29:43"
> 131;"2005-04-03 04:29:43"
> 132;"2005-04-03 05:35:17"
>
> So if we turn off DST on both the client -AND- the server (for me, the
> server part happens by issuing a "set timezone='etc/gmt+7'" before I
> use the connection), we finally get what we're looking for - write,
> read, write, w/ proper values on both display and in the db when we're
> all done.
> -----------------------------------------------------
> 133;"2005-04-03 00:06:02"
> 134;"2005-04-03 02:29:43"
> 135;"2005-04-03 03:29:43"
> 136;"2005-04-03 04:35:17"
>
> 137;"2005-04-03 00:06:02"
> 138;"2005-04-03 02:29:43"
> 139;"2005-04-03 03:29:43"
> 140;"2005-04-03 04:35:17"
>
> In other words, nothing got munged.
>
> At this point, I'd like to submit a piece of code that will ensure
> that the server is operating in the same time zone as the client
> connection, taking DST settings into consideration as well (Dave
> should understand where this would go as he was playing around with
> this type of thing when helping me debug last week):
>
> //in order to avoid munging dates on insert, we need to set
> the server to the same timezone
> //as the client for the duration of this connection. If
> daylight savings is turned on, this is
> //simple: just send the current timezone; if dst is turned
> off, however, then we need to send the GMT
> //equivalent (w/ no DST offset), which takes the form of
> etc/gmt+X (where X is the number of hours)
> TimeZone tz = TimeZone.getDefault();
> int roff = tz.getRawOffset();
> if (tz.useDaylightTime()) {
> stmt.execute("set timezone='"+tz.getID().toLowerCase()+"'");
> } else {
> stmt.execute("set timezone='etc/gmt"+(roff<=0 ? "+" :
> "")+(roff/-3600000)+"'");
> }
>
> This piece of code only needs to get executed when the connection is
> first opened to the DB, and from that point on it will ensure that the
> db is in functionally the same timezone as the server (taking DST
> on/off into account as well). In other words, if my client is running
> in MST w/ DST turned off, the server will be configured to etc/gmt+7,
> and any dates I insert will be left untouched.
>
> That make sense?
>
> If you'd like to see an example of the strings generated for each
> timezone, here's a snippet...
>
> String zones[] = TimeZone.getAvailableIDs();
> for (int i=0; i<zones.length; i++) {
> TimeZone tz = TimeZone.getTimeZone(zones[i]);
> int roff = tz.getRawOffset();
> if (!tz.useDaylightTime()) {
> System.out.println("server: 'etc/gmt"+(roff<=0 ? "+" :
> "")+(roff/-3600000)+"' ... "+i+": "+tz);
> } else {
> System.out.println("server: '"+tz.getID().toLowerCase()+"' ...
> "+i+": "+tz);
> }
> }
>
> Ok, so here's the summary:
>
> a) w/ DST turned on on the client, the client munges t2 forward to a
> valid time and inserts into the db (so we read 02:29 and write 03:29)
>
> b) w/ DST turned off on the client, the client does not munge t2, and
> neither does the db because its been tweaked to match (so we read
> 02:29 and write 02:29)
>
> So if I want to insert dates without munging, all I have to do is turn
> DST off in the client (and the example below illustrates how to do
> this for the current timezone).
>
> I'd like to request that we apply this patch to the JDBC drivers
> (again, Dave will know where to put it). This should not break any
> existing code, and it will make it possible to ensure that client code
> can insert dates without munging, and without forcing DST to be turned
> off systemwide on the server - it basically allows us to drive the
> setting on a per connection basis.
>
> Any feedback?
>
> Thanks,
> Christian
>
> (and thanks especially to Dave for helping me debug all this stuff
> over the past 10 days)
>
> -------------------------------------------------
> Code snippet to duplicate problem
> -------------------------------------------------
> //see what time zone we are running in (and optionally turn off DST)
> TimeZone curTz = TimeZone.getDefault();
> TimeZone curTzNoDST = new SimpleTimeZone(curTz.getRawOffset(),
> curTz.getID());
> //uncomment the line below to see what happens w/ DST turned off
> // TimeZone.setDefault(curTzNoDST); //this will turn off DST in
> the local JVM
> System.out.println("current tz:"+TimeZone.getDefault());
>
> //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 (?)");
>
> //drop, create the table
> try {stmt.execute("TRUNCATE TABLE Foo");}
> catch (SQLException e) {stmt.execute("CREATE TABLE Foo (UID
> SERIAL, TrxTime timestamp without time zone NOT NULL);");}
>
> //in order to avoid munging dates on insert, we need to set
> the server to the same timezone
> //as the client for the duration of this connection. If
> daylight savings is turned on, this is
> //simple: just send the current timezone; if dst is turned
> off, however, then we need to send the GMT
> //equivalent (w/ no DST offset), which takes the form of
> etc/gmt+X (where X is the number of hours)
> TimeZone tz = TimeZone.getDefault();
> int roff = tz.getRawOffset();
> if (tz.useDaylightTime()) {
> stmt.execute("set timezone='"+tz.getID().toLowerCase()+"'");
> } else {
> stmt.execute("set timezone='etc/gmt"+(roff<=0 ? "+" :
> "")+(roff/-3600000)+"'");
> }
>
> //now start with raw inserts (via statement) - these will
> correspond to the 1st 3 rows of data in the db
> System.out.println();
> stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> 00:06:02.0')");
> stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> 02:29:43.0')");
> stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> 03:29:43.0')");
> stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> 04:35:17.0')");
> System.out.println("insert-1 t1: 2005-04-03 00:06:02.0
> (millis: "+Timestamp.valueOf("2005-04-03 00:06:02.0").getTime()+")");
> System.out.println("insert-1 t2: 2005-04-03 02:29:43.0
> (millis: "+Timestamp.valueOf("2005-04-03 02:29:43.0").getTime()+")");
> System.out.println("insert-1 t3: 2005-04-03 03:29:43.0
> (millis: "+Timestamp.valueOf("2005-04-03 03:29:43.0").getTime()+")");
> System.out.println("insert-1 t4: 2005-04-03 04:35:17.0
> (millis: "+Timestamp.valueOf("2005-04-03 04:35:17.0").getTime()+")");
>
> //now read the table, getting the data we just inserted (in
> your output, you will notice the timestamps differ)
> System.out.println();
> ResultSet rs = stmt.executeQuery("SELECT * FROM Foo");
> List rows = new ArrayList();
> int cntr = 0;
> while (rs.next()) {
> Object uid = rs.getObject(1);
> t = rs.getTimestamp(2);
> System.out.println("result-1 t"+(++cntr)+": "+t+" (millis:
> "+t.getTime()+")");
> rows.add(new Object[] {uid, t});
> }
> rs.close();
>
> //now iterate through the sample data and re-insert
> System.out.println();
> Iterator it = rows.iterator();
> cntr = 0;
> while (it.hasNext()) {
> Object[] cols = (Object[]) it.next();
> t = (Timestamp) cols[1];
> pstmt.setTimestamp(1, t);
> System.out.println("insert-2 t"+(++cntr)+": "+t+" (millis:
> "+t.getTime()+")");
> pstmt.executeUpdate();
> }
>
> //now read the values back out (here we'll get all 6 values
> out; the last 3 correspond to 2nd set of inserts)
> System.out.println();
> rs = stmt.executeQuery("SELECT * FROM Foo");
> cntr = 0;
> while (rs.next()) {
> t = rs.getTimestamp(2);
> System.out.println("result-2 t"+(++cntr)+": "+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 2: Don't 'kill -9' the postmaster
On re-reading Christian's email, I see that he is only proposing to
change the JVM's time zone once, not once per connection. Still, having
a JDBC driver tinkering with JVM configurations like this is just not an
option for me. If we fix the other problems, he could do what he wants
by changing to a non-DST time zone in the enclosing application.
-Kevin
>>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> 07/25/05 12:13 PM >>>
As someone who is interested in timestamp columns only to hold actual
moments in time, I'm very uncomfortable with Christian's proposed "fix".
We have a highly multithreaded environment dealing with multiple
database servers. Having various threads all setting the default
timezone for the JVM to something inaccurate based on connections to a
variety of servers seems likely to break much more than it fixes. The
primary "problem" being solved by this technique is that it is hard to
record a timestamp representing a moment which doesn't exist any more
than do the following:
2005-02-29 00:00:00.0
2005-10-35 00:00:00.0
2005-10-25 00:75:00.0
It is fine with me if moments that don't exist can't be stored in the
database. Others have pointed out problems with storage and retrieval
of valid Timestamp objects. Those seem to me to be the problems to
address. I think that would go part of the way toward addressing
Christian's problems; but, since you can't actually create a Timestamp
object within a JVM set to the correct time zone to represent what he
wants, his particular issue will always require munging the Java runtime
environment, which is simply not an option in many situations.
-Kevin
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
UNSUBSCRIBE
Hi Kevin, On 7/25/05, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > As someone who is interested in timestamp columns only to hold actual > moments in time, I'm very uncomfortable with Christian's proposed "fix". But this isn't how the DB works...from the command line sql interface, or via the Statement implementation, you can easily insert "invalid" (eg. not-valid-DST) timestamps. So how does this mesh with my data integrity concerns - if I read a timestamp from jdbc, and then turn around and write that same timestamp, it seems to me the object shouldn't get munged. And right now, it does. > since you can't actually create a Timestamp object within > a JVM set to the correct time zone to represent what he wants Just to be clear - you CAN create a Timestamp for these objects (it just requires having DST turned off in order to do it). And that's really the rub - the DB contains data that Timestamp thinks is invalid (unless DST is turned off). We need something more than a "configure both your client and server to use the same non-DST timezone", which is currently the only option (although my suggestion still requires us to set the client into non-DST programatically). All that said, I am still basically sympathetic with your concern. It seems a bit hacky to me too, to be forcing the timezone on the server, just so date munging doesn't happen. I'd still like a solution where I can re-insert the date without munging, even if the server and the client are both running w/ DST turned on. So if someone can think of a way to do that, that would be even better... Christian
It's somewhat easier to track the servers timezone, as opposed to setting it The server will issue a notice and the new timezone when/if it is changed I've tried hacking that in, but I think there is some weirdness in the driver where we convert back and forth. I'd ideally like to use joda-time for the conversion, however I'm not sure including someone else's library is a good thing. Dave On 25-Jul-05, at 1:27 PM, Kevin Grittner wrote: > On re-reading Christian's email, I see that he is only proposing to > change the JVM's time zone once, not once per connection. Still, > having > a JDBC driver tinkering with JVM configurations like this is just > not an > option for me. If we fix the other problems, he could do what he > wants > by changing to a non-DST time zone in the enclosing application. > > -Kevin > > > >>>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> 07/25/05 12:13 PM >>>> >>> >>>> > As someone who is interested in timestamp columns only to hold actual > moments in time, I'm very uncomfortable with Christian's proposed > "fix". > We have a highly multithreaded environment dealing with multiple > database servers. Having various threads all setting the default > timezone for the JVM to something inaccurate based on connections to a > variety of servers seems likely to break much more than it fixes. The > primary "problem" being solved by this technique is that it is hard to > record a timestamp representing a moment which doesn't exist any more > than do the following: > > 2005-02-29 00:00:00.0 > 2005-10-35 00:00:00.0 > 2005-10-25 00:75:00.0 > > It is fine with me if moments that don't exist can't be stored in the > database. Others have pointed out problems with storage and retrieval > of valid Timestamp objects. Those seem to me to be the problems to > address. I think that would go part of the way toward addressing > Christian's problems; but, since you can't actually create a Timestamp > object within a JVM set to the correct time zone to represent what he > wants, his particular issue will always require munging the Java > runtime > environment, which is simply not an option in many situations. > > -Kevin > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > > ---------------------------(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 > >
Sorry, spoke before I finished thinking. Ignore this as line noise.
-- Mark
On Mon, 2005-07-25 at 10:16 -0700, Mark Lewis wrote:
> If this code only executes when the connection is established, then it
> will break for any long-lived connection that lives across a change in
> DST.
>
> This could be fixed by moving this code from connection init to just
> before we send any queries to the back end, sort of like:
>
> if(defaultTz.useDaylightTime() != oldUseDaylightTime) {
> runDSTCorrectionCode();
> }
>
> I don't know how cheap the useDaylightTime() call is, so I'm not sure
> what the performance impact of doing this on every query would be like.
> If it's bad, then maybe we'd need to program in some other way to know
> when DST turns on/off.
>
> On Mon, 2005-07-25 at 10:45 -0600, Christian Cryder wrote:
> > Hey folks,
> >
> > I just wanted to take a few moments and summarize where we are in our
> > Timestamp woes issue. I'm also going to post a piece of code that
> > illustrates some of the problems we've experienced, and then try and
> > explain the conclusions we've arrived at. Hopefully this well help
> > others in the future.
> >
> > First of all, the code snippet to easily duplicate the problem (see
> > the bottom of this email). What this code does is create a table Foo,
> > and then it inserts 4 rows using a SQL Statement. It then turns around
> > and reads those values via JDBC, and then simply re-inserts the values
> > back into the DB. As the example illustrates, the data written is NOT
> > the same as the data read (you can see this by querying the table
> > through the sql console after you've run the example).
> >
> > The key point to note here is that the 4 dates being inserted
> > originally fall immediately around and in the daylight savings cutover
> > (2-3 AM, 1st Sunday of April). So t1 is before daylight savings, t2 is
> > during the rollover (and thus technically not a valid daylight savings
> > time), t3 and t4 are after the rollover.
> >
> > With DST turned on on the client, here's the results in the table...
> > ----------------------------------------------------
> > 101;"2005-04-03 00:06:02"
> > 102;"2005-04-03 02:29:43"
> > 103;"2005-04-03 03:29:43"
> > 104;"2005-04-03 04:35:17"
> >
> > 105;"2005-04-03 00:06:02"
> > 106;"2005-04-03 03:29:43"
> > 107;"2005-04-03 03:29:43"
> > 108;"2005-04-03 04:35:17"
> >
> > This looks pretty good except that the original t2 (02:29) got munged
> > to 03:29 in the DB..
> >
> > With DST programatically turned off on the client, the dates display
> > properly in the client output (no munging), but they still get munged
> > on the server side of things (because there, DST is still turned on) -
> > and there, everything above the cutoff gets adjusted.
> > -----------------------------------------------------
> > 125;"2005-04-03 00:06:02"
> > 126;"2005-04-03 02:29:43"
> > 127;"2005-04-03 03:29:43"
> > 128;"2005-04-03 04:35:17"
> >
> > 129;"2005-04-03 00:06:02"
> > 130;"2005-04-03 03:29:43"
> > 131;"2005-04-03 04:29:43"
> > 132;"2005-04-03 05:35:17"
> >
> > So if we turn off DST on both the client -AND- the server (for me, the
> > server part happens by issuing a "set timezone='etc/gmt+7'" before I
> > use the connection), we finally get what we're looking for - write,
> > read, write, w/ proper values on both display and in the db when we're
> > all done.
> > -----------------------------------------------------
> > 133;"2005-04-03 00:06:02"
> > 134;"2005-04-03 02:29:43"
> > 135;"2005-04-03 03:29:43"
> > 136;"2005-04-03 04:35:17"
> >
> > 137;"2005-04-03 00:06:02"
> > 138;"2005-04-03 02:29:43"
> > 139;"2005-04-03 03:29:43"
> > 140;"2005-04-03 04:35:17"
> >
> > In other words, nothing got munged.
> >
> > At this point, I'd like to submit a piece of code that will ensure
> > that the server is operating in the same time zone as the client
> > connection, taking DST settings into consideration as well (Dave
> > should understand where this would go as he was playing around with
> > this type of thing when helping me debug last week):
> >
> > //in order to avoid munging dates on insert, we need to set
> > the server to the same timezone
> > //as the client for the duration of this connection. If
> > daylight savings is turned on, this is
> > //simple: just send the current timezone; if dst is turned
> > off, however, then we need to send the GMT
> > //equivalent (w/ no DST offset), which takes the form of
> > etc/gmt+X (where X is the number of hours)
> > TimeZone tz = TimeZone.getDefault();
> > int roff = tz.getRawOffset();
> > if (tz.useDaylightTime()) {
> > stmt.execute("set timezone='"+tz.getID().toLowerCase()+"'");
> > } else {
> > stmt.execute("set timezone='etc/gmt"+(roff<=0 ? "+" :
> > "")+(roff/-3600000)+"'");
> > }
> >
> > This piece of code only needs to get executed when the connection is
> > first opened to the DB, and from that point on it will ensure that the
> > db is in functionally the same timezone as the server (taking DST
> > on/off into account as well). In other words, if my client is running
> > in MST w/ DST turned off, the server will be configured to etc/gmt+7,
> > and any dates I insert will be left untouched.
> >
> > That make sense?
> >
> > If you'd like to see an example of the strings generated for each
> > timezone, here's a snippet...
> >
> > String zones[] = TimeZone.getAvailableIDs();
> > for (int i=0; i<zones.length; i++) {
> > TimeZone tz = TimeZone.getTimeZone(zones[i]);
> > int roff = tz.getRawOffset();
> > if (!tz.useDaylightTime()) {
> > System.out.println("server: 'etc/gmt"+(roff<=0 ? "+" :
> > "")+(roff/-3600000)+"' ... "+i+": "+tz);
> > } else {
> > System.out.println("server: '"+tz.getID().toLowerCase()+"' ...
> > "+i+": "+tz);
> > }
> > }
> >
> > Ok, so here's the summary:
> >
> > a) w/ DST turned on on the client, the client munges t2 forward to a
> > valid time and inserts into the db (so we read 02:29 and write 03:29)
> >
> > b) w/ DST turned off on the client, the client does not munge t2, and
> > neither does the db because its been tweaked to match (so we read
> > 02:29 and write 02:29)
> >
> > So if I want to insert dates without munging, all I have to do is turn
> > DST off in the client (and the example below illustrates how to do
> > this for the current timezone).
> >
> > I'd like to request that we apply this patch to the JDBC drivers
> > (again, Dave will know where to put it). This should not break any
> > existing code, and it will make it possible to ensure that client code
> > can insert dates without munging, and without forcing DST to be turned
> > off systemwide on the server - it basically allows us to drive the
> > setting on a per connection basis.
> >
> > Any feedback?
> >
> > Thanks,
> > Christian
> >
> > (and thanks especially to Dave for helping me debug all this stuff
> > over the past 10 days)
> >
> > -------------------------------------------------
> > Code snippet to duplicate problem
> > -------------------------------------------------
> > //see what time zone we are running in (and optionally turn off DST)
> > TimeZone curTz = TimeZone.getDefault();
> > TimeZone curTzNoDST = new SimpleTimeZone(curTz.getRawOffset(),
> > curTz.getID());
> > //uncomment the line below to see what happens w/ DST turned off
> > // TimeZone.setDefault(curTzNoDST); //this will turn off DST in
> > the local JVM
> > System.out.println("current tz:"+TimeZone.getDefault());
> >
> > //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 (?)");
> >
> > //drop, create the table
> > try {stmt.execute("TRUNCATE TABLE Foo");}
> > catch (SQLException e) {stmt.execute("CREATE TABLE Foo (UID
> > SERIAL, TrxTime timestamp without time zone NOT NULL);");}
> >
> > //in order to avoid munging dates on insert, we need to set
> > the server to the same timezone
> > //as the client for the duration of this connection. If
> > daylight savings is turned on, this is
> > //simple: just send the current timezone; if dst is turned
> > off, however, then we need to send the GMT
> > //equivalent (w/ no DST offset), which takes the form of
> > etc/gmt+X (where X is the number of hours)
> > TimeZone tz = TimeZone.getDefault();
> > int roff = tz.getRawOffset();
> > if (tz.useDaylightTime()) {
> > stmt.execute("set timezone='"+tz.getID().toLowerCase()+"'");
> > } else {
> > stmt.execute("set timezone='etc/gmt"+(roff<=0 ? "+" :
> > "")+(roff/-3600000)+"'");
> > }
> >
> > //now start with raw inserts (via statement) - these will
> > correspond to the 1st 3 rows of data in the db
> > System.out.println();
> > stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> > 00:06:02.0')");
> > stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> > 02:29:43.0')");
> > stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> > 03:29:43.0')");
> > stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> > 04:35:17.0')");
> > System.out.println("insert-1 t1: 2005-04-03 00:06:02.0
> > (millis: "+Timestamp.valueOf("2005-04-03 00:06:02.0").getTime()+")");
> > System.out.println("insert-1 t2: 2005-04-03 02:29:43.0
> > (millis: "+Timestamp.valueOf("2005-04-03 02:29:43.0").getTime()+")");
> > System.out.println("insert-1 t3: 2005-04-03 03:29:43.0
> > (millis: "+Timestamp.valueOf("2005-04-03 03:29:43.0").getTime()+")");
> > System.out.println("insert-1 t4: 2005-04-03 04:35:17.0
> > (millis: "+Timestamp.valueOf("2005-04-03 04:35:17.0").getTime()+")");
> >
> > //now read the table, getting the data we just inserted (in
> > your output, you will notice the timestamps differ)
> > System.out.println();
> > ResultSet rs = stmt.executeQuery("SELECT * FROM Foo");
> > List rows = new ArrayList();
> > int cntr = 0;
> > while (rs.next()) {
> > Object uid = rs.getObject(1);
> > t = rs.getTimestamp(2);
> > System.out.println("result-1 t"+(++cntr)+": "+t+" (millis:
> > "+t.getTime()+")");
> > rows.add(new Object[] {uid, t});
> > }
> > rs.close();
> >
> > //now iterate through the sample data and re-insert
> > System.out.println();
> > Iterator it = rows.iterator();
> > cntr = 0;
> > while (it.hasNext()) {
> > Object[] cols = (Object[]) it.next();
> > t = (Timestamp) cols[1];
> > pstmt.setTimestamp(1, t);
> > System.out.println("insert-2 t"+(++cntr)+": "+t+" (millis:
> > "+t.getTime()+")");
> > pstmt.executeUpdate();
> > }
> >
> > //now read the values back out (here we'll get all 6 values
> > out; the last 3 correspond to 2nd set of inserts)
> > System.out.println();
> > rs = stmt.executeQuery("SELECT * FROM Foo");
> > cntr = 0;
> > while (rs.next()) {
> > t = rs.getTimestamp(2);
> > System.out.println("result-2 t"+(++cntr)+": "+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 2: Don't 'kill -9' the postmaster
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
Hi Christian,
I don't know what this value is meant to convey, semantically. In your
time zone, there was no such moment. If you were converting from a
database which allowed October 35th in a timestamp column, would you
feel compelled to preserve the value in the new database, or fix it? If
it's from a different timezone, it doesn't tell you what moment it
represents without knowing which timezone. It seems like you've worked
around this by munging your runtime environment to something other than
the actual timezone it would normally have. As long as this value sits
in your database, every client which might want to read it (or similar
values) must munge the runtime environment.
What I'm proposing is that we need a fix so that when mapping a
Timestamp object, which always represents an unambiguous point in time,
to a "timestamp with time zone" value on the server (which also
represents an unambiguous point in time), that they match, and when
mapping a Timestamp object to a "timestamp without time zone" value on
the server, that the client specify which time zone's representation of
the moment to use.
This would give you what you want by simply setting the time zone for
your client JVM to a non-DST value -- the server setting wouldn't
matter. I think it would also solve the problems reported by others.
-Kevin
>>> Christian Cryder <c.s.cryder@gmail.com> 07/25/05 12:47 PM >>>
Hi Kevin,
On 7/25/05, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
> As someone who is interested in timestamp columns only to hold actual
> moments in time, I'm very uncomfortable with Christian's proposed
"fix".
But this isn't how the DB works...from the command line sql interface,
or via the Statement implementation, you can easily insert "invalid"
(eg. not-valid-DST) timestamps. So how does this mesh with my data
integrity concerns - if I read a timestamp from jdbc, and then turn
around and write that same timestamp, it seems to me the object
shouldn't get munged. And right now, it does.
> since you can't actually create a Timestamp object within
> a JVM set to the correct time zone to represent what he wants
Just to be clear - you CAN create a Timestamp for these objects (it
just requires having DST turned off in order to do it). And that's
really the rub - the DB contains data that Timestamp thinks is invalid
(unless DST is turned off).
We need something more than a "configure both your client and server
to use the same non-DST timezone", which is currently the only option
(although my suggestion still requires us to set the client into
non-DST programatically).
All that said, I am still basically sympathetic with your concern. It
seems a bit hacky to me too, to be forcing the timezone on the server,
just so date munging doesn't happen. I'd still like a solution where I
can re-insert the date without munging, even if the server and the
client are both running w/ DST turned on. So if someone can think of a
way to do that, that would be even better...
Christian
---------------------------(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
Hi Dave, I thought I addressed that in the long paragraph near the bottom of this message. http://archives.postgresql.org/pgsql-jdbc/2005-07/msg00283.php This seems to me that it doesn't require any new datatypes and doesn't require that we know the type on the server side ahead of time. Am I missing something? -Kevin >>> Dave Cramer <davec@postgresintl.com> 07/25/05 1:43 PM >>> The challenge with this, is that we don't know ahead of time what type the underlying data is. If we did this is a trivial problem. Right now we bind the parameter in the statement to a timestamptz type. If we knew ahead of time, we could easily bind it to a timestamp. The simplest solution that Christian has is to create two types that extend PGobject and do exactly as above.
On 25-Jul-05, at 2:17 PM, Kevin Grittner wrote: > Hi Christian, > > I don't know what this value is meant to convey, semantically. In > your > time zone, there was no such moment. If you were converting from a > database which allowed October 35th in a timestamp column, would you > feel compelled to preserve the value in the new database, or fix > it? If > it's from a different timezone, it doesn't tell you what moment it > represents without knowing which timezone. It seems like you've > worked > around this by munging your runtime environment to something other > than > the actual timezone it would normally have. As long as this value > sits > in your database, every client which might want to read it (or similar > values) must munge the runtime environment. > > What I'm proposing is that we need a fix so that when mapping a > Timestamp object, which always represents an unambiguous point in > time, > to a "timestamp with time zone" value on the server (which also > represents an unambiguous point in time), that they match, and when > mapping a Timestamp object to a "timestamp without time zone" value on > the server, that the client specify which time zone's > representation of > the moment to use. > The challenge with this, is that we don't know ahead of time what type the underlying data is. If we did this is a trivial problem. Right now we bind the parameter in the statement to a timestamptz type. If we knew ahead of time, we could easily bind it to a timestamp. The simplest solution that Christian has is to create two types that extend PGobject and do exactly as above. > > This would give you what you want by simply setting the time zone for > your client JVM to a non-DST value -- the server setting wouldn't > matter. I think it would also solve the problems reported by others. > > -Kevin > > > > >>>> Christian Cryder <c.s.cryder@gmail.com> 07/25/05 12:47 PM >>> >>>> >>>> > Hi Kevin, > > On 7/25/05, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > > >> As someone who is interested in timestamp columns only to hold actual >> moments in time, I'm very uncomfortable with Christian's proposed >> >> > "fix". > > But this isn't how the DB works...from the command line sql interface, > or via the Statement implementation, you can easily insert "invalid" > (eg. not-valid-DST) timestamps. So how does this mesh with my data > integrity concerns - if I read a timestamp from jdbc, and then turn > around and write that same timestamp, it seems to me the object > shouldn't get munged. And right now, it does. > > > >> since you can't actually create a Timestamp object within >> a JVM set to the correct time zone to represent what he wants >> >> > > Just to be clear - you CAN create a Timestamp for these objects (it > just requires having DST turned off in order to do it). And that's > really the rub - the DB contains data that Timestamp thinks is invalid > (unless DST is turned off). > > We need something more than a "configure both your client and server > to use the same non-DST timezone", which is currently the only option > (although my suggestion still requires us to set the client into > non-DST programatically). > > All that said, I am still basically sympathetic with your concern. It > seems a bit hacky to me too, to be forcing the timezone on the server, > just so date munging doesn't happen. I'd still like a solution where I > can re-insert the date without munging, even if the server and the > client are both running w/ DST turned on. So if someone can think of a > way to do that, that would be even better... > > Christian > > ---------------------------(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 > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > >
Christian Cryder wrote:
> I just wanted to take a few moments and summarize where we are in our
> Timestamp woes issue. I'm also going to post a piece of code that
> illustrates some of the problems we've experienced, and then try and
> explain the conclusions we've arrived at. Hopefully this well help
> others in the future.
Why is another thread on this necessary?
Did you try the patch I sent you off-list?
> if (tz.useDaylightTime()) {
> stmt.execute("set timezone='"+tz.getID().toLowerCase()+"'");
> } else {
> stmt.execute("set timezone='etc/gmt"+(roff<=0 ? "+" :
> "")+(roff/-3600000)+"'");
> }
I have real problems with fiddling with the server timezone as discussed
in the other thread, and I'd rather not see this patch applied. It makes
a heck of a lot of assumptions about how Java/server timezones match up,
and we have other options (namely using Unknown parameter types) that
seem better.
-O
On 7/25/05, Christian Cryder <c.s.cryder@gmail.com> wrote: > [...] > This piece of code only needs to get executed when the connection is > first opened to the DB, and from that point on it will ensure that the > db is in functionally the same timezone as the server (taking DST > on/off into account as well). In other words, if my client is running > in MST w/ DST turned off, the server will be configured to etc/gmt+7, > and any dates I insert will be left untouched. > > That make sense? Hi Christian, Besides setTimestamp(int, Timestamp) and getTimestamp(int) we have the more general methods setTimestamp(int, Timestamp, Calendar) and getTimestamp(int, Calendar). If I understand the specs correctly, calls to the methods without a Calendar parameter should be equivalent to the call to the other methods with a Calendar that has time zone TimeZone.getDefault(). From this it follows that modifying the VM's default time zone is a non-solution. Moreover in my opinion the database server's time zone should be ignored by any client (application and JDBC driver) code. Regards, Alex
Christian Cryder wrote: > ------------------------------------------------- > Code snippet to duplicate problem > ------------------------------------------------- I couldn't work out exactly what this was meant to be showing, and it appears to make a number of assumptions about local timezone it doesn't seem to useful. (most notably, NZ daylight savings isn't the same as the north american setup..) Can you put together a test case that explicitly sets the JVM timezone and the server timezones to something you know will break, and then *tests* the output i.e. says "I expected value 1 but I got value 2 instead! bad!". Then I can actually test my patches to the driver against the exact case you have problems with.. -O
Hi Oliver,
On 7/25/05, Oliver Jowett <oliver@opencloud.com> wrote:
> Christian Cryder wrote:
> > I just wanted to take a few moments and summarize where we are in our
> > Timestamp woes issue. I'm also going to post a piece of code that
> > illustrates some of the problems we've experienced, and then try and
> > explain the conclusions we've arrived at. Hopefully this well help
> > others in the future.
>
> Why is another thread on this necessary?
Uh, just to try and concisely summarize what I had found? I wasn't
trying to start another discussion or anything...
> Did you try the patch I sent you off-list?
I don't think I received a patch from you off list.
I did talk to Dave this morning, who emailed me a new jar (which
contained your patch). And when I tried it, it works fine (except for
the fact that we still have to turn DST off on the client and the
configure the server timezone accordingly if we want to prevent data
munging. I'd rather I didn't have to do that, but I can live with it.
So I'm fine at this point...)
> > if (tz.useDaylightTime()) {
> > stmt.execute("set timezone='"+tz.getID().toLowerCase()+"'");
> > } else {
> > stmt.execute("set timezone='etc/gmt"+(roff<=0 ? "+" :
> > "")+(roff/-3600000)+"'");
> > }
>
> I have real problems with fiddling with the server timezone as discussed
> in the other thread, and I'd rather not see this patch applied. It makes
> a heck of a lot of assumptions about how Java/server timezones match up,
> and we have other options (namely using Unknown parameter types) that
> seem better.
If you can think of another way to do it, I'm all ears. Here's the
crux of the issue (I feel like I've said this so many times now that
I'm beginning to wonder if there is something fundamentally unclear in
how I'm saying it).
a) in a TIMESTAMP WITHOUT TIMEZONE column, 04-03-2005 02:29:00 is a
perfectly valid time (you can manually insert this value from the
console, or via a Statement)
b) JDBC maps all TIMESTAMP columns to java.sql.Timestamp, which HAS
the notion of Timezone in it. Meaning this date is going to get munged
(because it's not a valid time in MST, for instance) as soon as you
read it from the db, unless you set DST off.
c) even if you set DST off in the client, if its still turned on in
the server your date will still get munged on re-insert (even in your
code that I tested this morning).
d) we don't think we should have to turn DST off in both client and
server in order to guarantee that we can read values from the DB and
rewrite them untouched (as the same values).
So that's the long and the short of it - we're in MST, we have that
02:29 value in a DB column, we'd just like to be able to read it and
rewrite it without having to reconfigure either client or server to
some funky time zone. If you can suggest some other way of making that
happen I'd love to hear it.
Now, just to be clear - we can live with it this way. Currently w/ MS
SQL 2000 and NetDirect JDBC drivers, we have to do something similar -
configure the client jvm to non-DST in order to read/write w/out
munging. On Postrgres, we just have to add the one additional set of
setting the server timezone before reconnecting. So functionally, the
behavior is very close. And we're ok with this - our code is already
modified, etc.
But that's the problem that we'd still like to see a better solution for.
Please let me know if the problem still is not clear... (Dave, maybe
you can chime in if you see some ambiguity in my description, since I
think you have a handle on the issue we're wrestling with)
Thanks much for all your help guys...
Christian
Christian Cryder wrote:
> If you can think of another way to do it, I'm all ears. Here's the
> crux of the issue (I feel like I've said this so many times now that
> I'm beginning to wonder if there is something fundamentally unclear in
> how I'm saying it).
> a) in a TIMESTAMP WITHOUT TIMEZONE column, 04-03-2005 02:29:00 is a
> perfectly valid time (you can manually insert this value from the
> console, or via a Statement)
Well I was expecting you to deal with it like this:
TimeZone utc = TimeZone.getTimeZone("UTC");
Calendar utcCal = Calendar.getInstance(utc);
// ...
Timestamp ts = rs.getTimestamp(1, utcCal);
// Deal with ts in terms of utcCal, e.g.:
// utcCal.setTime(ts);
// hours = utcCal.get(Calendar.HOUR);
// etc
stmt.setTimestamp(1, ts, utcCal);
In theory that should work just fine for any timestamp with no need to
meddle with either the JVM timezone or the server timezone.
The trick here is that any timestamp can be represented in UTC correctly
since UTC doesn't do any sort of daylight savings.
This isn't going to work in the current driver because it royally screws
up handling the with-Calendar variants of get/setTimestamp, but it
should be OK with my patched version.
Does that solve your problem? Am I missing something here?
> b) JDBC maps all TIMESTAMP columns to java.sql.Timestamp, which HAS
> the notion of Timezone in it. Meaning this date is going to get munged
> (because it's not a valid time in MST, for instance) as soon as you
> read it from the db, unless you set DST off.
This (Timestamp knowing about TimeZone) isn't generally true. The only
timezone-specific bit of Timestamp is the deprecated method that lets
you get the local JVM's default timezone offset. There's no timezone
info actually stored in the Timestamp. You generally want to be using a
Timestamp plus Calendar pair if you want to deal with times in a
particular timezone.
If you're trying to use a timezone that can't represent some date/time
values (such as, by the sounds of it, your default JVM timezone), then
I'm not suprised you're having problems. But that's really a design
issue in your app more than anything -- you're asking Timestamp to do
something that it *can't*.
-O