Two millisecond timestamp offset

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Two millisecond timestamp offset

Adrian Cox
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(ServletBase.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(ServletBase.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 <[hidden email]>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: Two millisecond timestamp offset

Oliver Jowett
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
Reply | Threaded
Open this post in threaded view
|

Re: Two millisecond timestamp offset

Adrian Cox
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(SimpleTimeZone.UTC_TIME, "UTC");

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

--
Adrian Cox <[hidden email]>


---------------------------(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
Reply | Threaded
Open this post in threaded view
|

Re: Two millisecond timestamp offset

Vadim Nasardinov
In reply to this post by Adrian Cox
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