SEO

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("jdbcostgresql:"+dat abase,
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();
}


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

matthias.malsy@sme.de wrote:

> 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): [...]

The problem is that the raw value stored in the database is "31 Oct 2004
02:00:00" with no timezone information. There are two possible instants
in time in the CET/CEST timezone that match this:

(initially in CEST, UTC+02)
31 Oct 2004 02:00:00 CEST == 00:00:00 UTC
(at 3am, clocks go back an hour to 2am; now in CET, UTC+01)
31 Oct 2004 02:00:00 CET == 01:00:00 UTC

Java's date parser happens to pick the second case when given a raw "2am
local time" value.

So this is not a driver bug, it is something of a design flaw in your
schema, since "timestamp without time zone" cannot identify a unique
instant in local time when daylight savings is involved.

-O

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)