Skip site navigation (1) Skip section navigation (2)

Two millisecond timestamp offset

From: Adrian Cox <adrian(at)humboldt(dot)co(dot)uk>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Two millisecond timestamp offset
Date: 2005-09-09 11:18:30
Message-ID: 1126264710.3069.76.camel@localhost (view raw or flat)
Thread:
Lists: pgsql-jdbc
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 <adrian(at)humboldt(dot)co(dot)uk>


Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2005-09-09 14:25:36
Subject: Re: Two millisecond timestamp offset
Previous:From: Aydın ToprakDate: 2005-09-09 06:03:59
Subject: Re: simple insert operation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group