PostgreSQL JDBC - Two millisecond timestamp offset

Author

Two millisecond timestamp offset

Adrian Cox

2005-09-09, 7:24 am

I couldn't find anything on this with Google, but I've got a 2ms offset
between the java.sql.Timestamp representation and the string
representation of a "timestamp with time zone".

I've tried the following JDBC releases: 8.1dev-401 JDBC 3, 8.0-312 JDBC
3, pg74.216.jdbc3.jar. The server is the Debian package of 7.4.7, though
I've seen the same problem against Postgres 7.2.

Here's a section from my JAVA code:
DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
format.setCalendar(new GregorianCalendar(Se
rvletBase.UTC));
PreparedStatement stmt = db.prepareStatement("insert into test values(1,?)"); //create a statement that we can use later
Date date = format.parse("2005-05-12 17:14:21");
stmt.setTimestamp(1, new Timestamp(date.getTime()));
stmt.execute();
stmt = db.prepareStatement("select index, datetime from test");
Statement stmt2 = db.createStatement();
stmt2.executeUpdate("insert into test values(2, '2004-11-10 17:32:19')");
ResultSet rs = stmt.executeQuery();
DateFormat output = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
output.setCalendar(new GregorianCalendar(Se
rvletBase.UTC));
while(rs.next()) {
date = new Date(rs.getTimestamp(2).getTime());
System.out.println("Result " + rs.getInt(1) + " :- " + output.format(date));
}

The output from Java code is:
Result 1 :- 2005-05-12 17:14:21.000
Result 2 :- 2004-11-10 17:32:19.002

The database sees:
testcode=> select * from test;
index | datetime
-------+----------------------------
1 | 2005-05-12 17:14:20.998+00
2 | 2004-11-10 17:32:19+00
(2 rows)

--
Adrian Cox <adrian@humboldt.co.uk>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Oliver Jowett

2005-09-09, 9:27 am

Adrian Cox wrote:

> The output from Java code is:
> Result 1 :- 2005-05-12 17:14:21.000
> Result 2 :- 2004-11-10 17:32:19.002
>
> The database sees:
> testcode=> select * from test;
> index | datetime
> -------+----------------------------
> 1 | 2005-05-12 17:14:20.998+00
> 2 | 2004-11-10 17:32:19+00
> (2 rows)

This works correctly on my system, so there is something else going on here.

What is the JVM's default timezone? If you format the Date objects using
that timezone, what do you get?

The JDBC driver will use the JVM's default timezone to format dates
unless you explicitly pass a Calendar to setTimestamp() etc, so if that
timezone is mysteriously 2ms out then it'd explain the strange behaviour
you see.

-O

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Adrian Cox

2005-09-09, 11:24 am

On Sat, 2005-09-10 at 02:25 +1200, Oliver Jowett wrote:
> The JDBC driver will use the JVM's default timezone to format dates
> unless you explicitly pass a Calendar to setTimestamp() etc, so if that
> timezone is mysteriously 2ms out then it'd explain the strange behaviour
> you see.

Thanks for the clue. There was an error in the code that built the
shared SimpleTimeZone object used for all the date calculations. It was
cut and pasted in from some broken reference code:

new SimpleTimeZone(Simpl
eTimeZone.UTC_TIME, "UTC");

The value of SimpleTimeZone.UTC_TIME is actually 2, leading to a
mysterious two millisecond offset.

--
Adrian Cox <adrian@humboldt.co.uk>

---------------------------(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

Vadim Nasardinov

2005-09-09, 11:24 am

On Friday 09 September 2005 07:18, Adrian Cox wrote:
> I couldn't find anything on this with Google, but I've got a 2ms
> offset between the java.sql.Timestamp representation and the string
> representation of a "timestamp with time zone".
>
> I've tried the following JDBC releases: 8.1dev-401 JDBC 3, 8.0-312
> JDBC 3, pg74.216.jdbc3.jar. The server is the Debian package

Since you're a Debian user, there is a greater-than-zero chance you
may be using a free java runtime like GCJ or some such. If so, it
would be helpful if you could also mention the version of GCJ that
you're using. In old versions of GNU Classpath, the implementation of
java.sql.Timestamp was buggy. (It's a little tricky to get right due
to the fact that the fractional seconds - the "nanos" - are separate
from whole seconds. Care must be taken when converting this slightly
schizophrenic internal representation to/from milliseconds.)

The only specific bug I remember is this one:
http://gcc.gnu.org/bugzilla/show_bug.cgi?id=16574

There might have been others.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links