Dear pgjdbc users and developers,
hope you can help me fixing my daylight safing problem.
Thx in advance
Matthias
Description:
------------
I reduced my problem to a simple testcase just storing and loading a date
from the database. The tescase fails for the time interval of
2004-10-31 00:00:00 to 01:00:00 (UTC). In order to stay ANSI conform,
I use the 'timestamp [without timezone]' data type.
- create table mdso_demo (id int, d timestamp)
Scenario:
---------
I am storing sales data (containing sales date/time) in a database by
ignoring the timezone in order to make it comparable across multiple
timezones. The sales date is deliverd by a foreign system as a String
and is parsed by an UTC-SimpleDateFormatter. After safing and loading,
the date (and its milliseconds) differs by 1 hour.
Output (running on CET/CEST):
-----------------------------
-- Date parsed and stored (running on CET/CEST) -----------
Sun Oct 31 02:00:00 CEST 2004
1099180800000
31 Oct 2004 00:00:00 GMT
-- Date loaded from database
Sun Oct 31 02:00:00 CET 2004
1099184400000
31 Oct 2004 01:00:00 GMT
Environment:
------------
PG: PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.3.4 (pre 3.3.5 20040809)
JDBC: pg74.215.jdbc3.jar
JAVA: java version "1.4.2_06"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_06-b03)
Java HotSpot(TM) Client VM (build 1.4.2_06-b03, mixed mode)
Database _and_ Client are running at CET/CEST.
The Code:
---------
public void testDaylightSavings3() throws Exception {
String database = "bkgrsta_dev";
String username ="mat";
String password = "";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
TimeZone tzGMT = TimeZone.getTimeZone("etc/UTC");
sdf.setTimeZone( tzGMT );
Date testDate = sdf.parse("2004-10-31 00:00:00");
System.out.println("---------------- A Date -----------");
System.out.println(testDate);
System.out.println(testDate.getTime());
System.out.println(testDate.toGMTString());
Class.forName("org.postgresql.Driver"); //load the driver
Connection db =
DriverManager.getConnection("jdbc:postgresql:"+database,
username,
password); //connect to the db
Statement sql = db.createStatement(); //create a statement that we
can use later
String sqlText = "create table mdso_demo (id int, d timestamp)";
sql.executeUpdate(sqlText);
Timestamp ts = new Timestamp(testDate.getTime());
System.out.println("---------------- Converted to a Timestamp
----------------");
System.out.println(ts);
System.out.println(ts.getTime());
System.out.println(ts.toGMTString());
sqlText = "insert into mdso_demo values (?,?)";
PreparedStatement ps = db.prepareStatement(sqlText);
ps.setInt(1,1);
ps.setTimestamp(2,ts);
ps.execute();
System.out.println("----------------- Date has been inserted into
the database ----------");
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("select id, d from mdso_demo");
boolean b = rs.next();
Timestamp resTime = rs.getTimestamp(2);
System.out.println("--------------- Timestamp loaded from database
-----------");
System.out.println(resTime);
System.out.println(resTime.getTime());
System.out.println(resTime.toGMTString());
Date resDate = new Date(resTime.getTime());
System.out.println("--------------- Converted to a date
-----------------");
System.out.println(resDate);
System.out.println(resDate.getTime());
System.out.println(resDate.toGMTString());
long diff = resDate.getTime() - testDate.getTime();
System.out.println("----------- difference was
------------------------- ");
System.out.println("ms: "+ diff + " h:" + diff /(1000*60*60));
rs.close();
sqlText ="drop table mdso_demo";
sql.execute(sqlText);
db.close();
}
