db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Antony Sequeira <antony.seque...@gmail.com>
Subject Re: time format (or: the curse of dst)
Date Sun, 25 Oct 2009 06:29:13 GMT
I usually lurk here and haven't dealt with derby in a long time.

I am posting this cause this seems to be a badly handled thing in not
just DBs but also many programming lang/libs.

Anyone storing local time needs to either store the offset info or at
least a boolean flag indicating whether dst was on or off. I know lots
of systems don't :)

A string based format (a single varchar or whatever field) would be
another option
2010/10/25T02:30:00-07:00
2010/10/25T02:30:00-08:00
would be the two distinct 2:30AMs on Oct 25 for the Pacific timezone
(use a + for timezones on the other side of GMT)
Just to be clear - this is for storage, not for user presentation

In a DB like MS sqlserver2008 that provides a native data type called
DateTimeOffset , you can use that.
FYI this is not an option in MS SQLServer2005. Just shows how bad the
situation in the overall industry is regarding this issue (if it takes
2008 for MS to recognize this), it's not like this requirement is new
stuff. May be other than telcos, no one had this issue before?

Regarding GMT, there are reasons for storing localtime even though in
theory you can compute it

-Antony


On Sat, Oct 24, 2009 at 2:12 AM, Fabio <pruefsum@me.com> wrote:
> Hello,
>
> I'm using Apache Derby to store hourly values of electricity consumption.
> The values are used in an electricity market simulation implemented in Java.
>
> Now because of DST, hourly values mean that (for central Europe):
> - one day in March has 23 hours (there's no 2 a.m. to 3 a.m. during that
> night)
> - one day in October has 25 hours (2 a.m. to 3 a.m. appears twice during
> that night)
>
> The data in October is published as:
> (..)
> 02:00
> 3A:00
> 3B:00
> 04:00
> 05:00
> (..)
>
> I have yet to find a good solution on how to store this data in the
> database. Obviously, "3A:00" is not a valid time format.
> Currently, I'm storing the consumption data in conjunction with a field
> named "hour_in_year" (1-8760) but having the date and time is more handy for
> sql select queries.
>
> Any ideas how to support the "curse of dst" in a time field?
>
> Fabio
>

Mime
View raw message