Thread: Timestamp confusion
I'm perplexed by the behaviour of ResultSet.getTimestamp() when passed
a calendar. If I create a table with two rows:
CREATE TABLE dtest (
uid integer NOT NULL,
d1 timestamp with time zone,
d2 timestamp without time zone
);
The code belows inserts a row first with the default calendar, then
with PST
Then it pulls the two rows just inserted back out, and prints them,
first with default, then with PST all using the default postgres
timezone. Then it attempts to change the connection's timezone via
SET TIMEZONE and prints the rows again.
The results are totally baffle me, as none of the results of the PST
seem to be "correct" to me. Is this expected behaviour? Is the only
way to force the PG/JDBC interface to "do the right thing". Even when
I try to SET the timezone manually, as done in the last example, the
results are still ale nonsense?
The timestamp with time zone seems to adjust the time correctly if I
SET the timezone manually with psql then do inserts/retrievals from
there, but I can not get that to work through the JDBC interface
either. I am curious how other Java developers support user specific
timezones in their applications with Postgres?
public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver"); //load the driver
Connection con =
DriverManager.getConnection("jdbc:postgresql:datetest","richard","password");
con.setAutoCommit(false);
Statement s = con.createStatement();
s.execute("DELETE FROM dtest");
Calendar local = new GregorianCalendar();
System.out.println("Default TZ: " +
TimeZone.getDefault().getDisplayName());
Timestamp ts = new Timestamp(new Date().getTime());
System.out.println("Application time: " + ts );
Calendar pst = new
GregorianCalendar( TimeZone.getTimeZone("PST"));
//now we insert two, one with default timezone, one with PST:
PreparedStatement ps = con.prepareStatement("INSERT INTO dtest
(d1,d2) VALUES (?,?)");
ps.setTimestamp(1,ts);
ps.setTimestamp(2,ts);
ps.execute();
ps.setTimestamp(1,ts, pst);
ps.setTimestamp(2,ts, pst);
ps.execute();
//now print them back out using default timezone
ResultSet rs = s.executeQuery("SELECT * FROM dtest");
System.out.println();
System.out.println("Default Timezone:");
while( rs.next() ){
System.out.println( "Row(" + rs.getInt(1) + ") d1("
+rs.getTimestamp(2) +") d2(" + rs.getTimestamp(3) +")");
}
rs = s.executeQuery("SELECT * FROM dtest");
System.out.println();
System.out.println("PST Timezone:");
while( rs.next() ){
System.out.println( "Row(" + rs.getInt(1) + ") d1("
+rs.getTimestamp(2,pst) +") d2(" + rs.getTimestamp(3,pst) +")");
}
s.executeUpdate("SET TIMEZONE TO 'PST8PDT';");
rs = s.executeQuery("SELECT * FROM dtest");
System.out.println();
System.out.println("PG Timezone adjust to PST8PDT, Default
Calendar:");
while( rs.next() ){
System.out.println( "Row(" + rs.getInt(1) + ") d1("
+rs.getTimestamp(2) +") d2(" + rs.getTimestamp(3) +")");
}
rs = s.executeQuery("SELECT * FROM dtest");
System.out.println();
System.out.println("PG Timezone adjust to PST8PDT, PST
Calendar");
while( rs.next() ){
System.out.println( "Row(" + rs.getInt(1) + ") d1("
+rs.getTimestamp(2,pst) +") d2(" + rs.getTimestamp(3,pst) +")");
}
}
Richard Rowell wrote: > I'm perplexed by the behaviour of ResultSet.getTimestamp() when passed > a calendar. I ran your testcase and it seems to be working fine. Here's my output: > > Default TZ: New Zealand Standard Time > Application time: 2010-02-13 10:15:31.327 > > Default Timezone: > Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327) > Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-12 13:15:31.327) > > PST Timezone: > Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-14 07:15:31.327) > Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327) > > PG Timezone adjust to PST8PDT, Default Calendar: > Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327) > Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-12 13:15:31.327) > > PG Timezone adjust to PST8PDT, PST Calendar > Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-14 07:15:31.327) > Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327) > CREATE TABLE dtest ( > uid integer NOT NULL, > d1 timestamp with time zone, > d2 timestamp without time zone > ); I made uid a SERIAL so your test code actually worked, and turned on autocommit so I could look at the results after the fact: testdb=# select * from dtest; uid | d1 | d2 -----+----------------------------+------------------------- 1 | 2010-02-13 10:15:31.327+13 | 2010-02-13 10:15:31.327 2 | 2010-02-13 10:15:31.327+13 | 2010-02-12 13:15:31.327 (2 rows) d1 identifies a particular instant in time, and you are always formatting it with the default calendar (because you're just using Timestamp.toString()), so it always ends up as exactly the same value when displayed, regardless of what Calendar is passed. d2 identifies a particular *clock time*, which is a different instant in time depending on what timezone you interpret it in. The server timezone is entirely irrelevant. The Calendar you pass determines what timezone it is interpreted in. So row 1's d2 is the clock time in the default timezone when the test was run. Row 2's d2 is the clock time in PST when the test was run. As you can see from the raw data shown by psql, row 2 is 21 hours behind row 1. That's right, because PST is 21 hours behind NZ. Then all the output for d2 just follows from that. When you pass the default calendar, you get Timestamps that are correct for the default calendar. Timestamp.toString() uses the default calendar, so those cases just reflect the values in the table directly. When you pass a PST Calendar when retrieving d2 from row 1, what you're saying is "give me a Timestamp for the instant in time that is 2010-02-13 10:15:31.327 in PST". This is not the same instant in time as when the test is run - it is 21 hours *later* than the test ran, i.e. 2010-02-14 07:15:31.327 in the NZ timezone. Then you print that value in the *default timezone*, which is essentially saying "what time is 2010-02-13 10:15:31.327 PST in the NZ timezone?". And it displays that correctly as 2010-02-14 07:15:31.327. The same sort of argument applies to row 2. Usually, you want to format timestamps using the same Calendar you used to retrieve them. You're not doing that in your test code - Timestamp stores no timezone information, and Timestamp.toString() always uses the default JVM timezone. I've written this explanation, or a similar one, many times in the past. You probably want to search the list archives. -O
Thank you Oliver for the followup. I did search the archives and read the group FAQ and even STFW before I posted, but obviously I have missed much. On Feb 12, 4:40 pm, oli...@opencloud.com (Oliver Jowett) wrote: > Richard Rowell wrote: > > I'm perplexed by the behaviour of ResultSet.getTimestamp() when passed > > a calendar. > > I ran your testcase and it seems to be working fine. Here's my output: > > > > > > > > > Default TZ: New Zealand Standard Time > > Application time: 2010-02-13 10:15:31.327 > > > Default Timezone: > > Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327) > > Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-12 13:15:31.327) > > > PST Timezone: > > Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-14 07:15:31.327) > > Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327) > > > PG Timezone adjust to PST8PDT, Default Calendar: > > Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327) > > Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-12 13:15:31.327) > > > PG Timezone adjust to PST8PDT, PST Calendar > > Row(1) d1(2010-02-13 10:15:31.327) d2(2010-02-14 07:15:31.327) > > Row(2) d1(2010-02-13 10:15:31.327) d2(2010-02-13 10:15:31.327) > > CREATE TABLE dtest ( > > uid integer NOT NULL, > > d1 timestamp with time zone, > > d2 timestamp without time zone > > ); > > I made uid a SERIAL so your test code actually worked, and turned on > autocommit so I could look at the results after the fact: > > testdb=# select * from dtest; > uid | d1 | d2 > -----+----------------------------+------------------------- > 1 | 2010-02-13 10:15:31.327+13 | 2010-02-13 10:15:31.327 > 2 | 2010-02-13 10:15:31.327+13 | 2010-02-12 13:15:31.327 > (2 rows) > > d1 identifies a particular instant in time, and you are always > formatting it with the default calendar (because you're just using > Timestamp.toString()), so it always ends up as exactly the same value > when displayed, regardless of what Calendar is passed. > > d2 identifies a particular *clock time*, which is a different instant in > time depending on what timezone you interpret it in. The server timezone > is entirely irrelevant. The Calendar you pass determines what timezone > it is interpreted in. > > So row 1's d2 is the clock time in the default timezone when the test > was run. > Row 2's d2 is the clock time in PST when the test was run. > > As you can see from the raw data shown by psql, row 2 is 21 hours behind > row 1. That's right, because PST is 21 hours behind NZ. > > Then all the output for d2 just follows from that. When you pass the > default calendar, you get Timestamps that are correct for the default > calendar. Timestamp.toString() uses the default calendar, so those cases > just reflect the values in the table directly. > > When you pass a PST Calendar when retrieving d2 from row 1, what you're > saying is "give me a Timestamp for the instant in time that is > 2010-02-13 10:15:31.327 in PST". This is not the same instant in time as > when the test is run - it is 21 hours *later* than the test ran, i.e. > 2010-02-14 07:15:31.327 in the NZ timezone. > > Then you print that value in the *default timezone*, which is > essentially saying "what time is 2010-02-13 10:15:31.327 PST in the NZ > timezone?". And it displays that correctly as 2010-02-14 07:15:31.327. > > The same sort of argument applies to row 2. > > Usually, you want to format timestamps using the same Calendar you used > to retrieve them. You're not doing that in your test code - Timestamp > stores no timezone information, and Timestamp.toString() always uses the > default JVM timezone. > > I've written this explanation, or a similar one, many times in the past. > You probably want to search the list archives. > > -O > > -- > Sent via pgsql-jdbc mailing list (pgsql-j...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-jdbc