Daylight Safing Problem 2004-10-31 00:00:00 (UTC) - Mailing list pgsql-jdbc

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();
    }