Re: Daylight Safing Problem 2004-10-31 00:00:00 (UTC)

matthias(dot)malsy(at)sme(dot)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