time format (or: the curse of dst)

[Fabio-52]

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

[Sylvain Leroux]

Hi Fabio,

Maybe this is overly simplistic, but why not storing *GMT* datetime?

In that eventuality, some custom written functions would also be necessary to
convert from/to your specific time format.

Sylvain

Fabio a écrit :

>
> 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
>
>

--
Website: http://www.chicoree.fr

[Bernt M. Johnsen]

>>>>>>>>>>>> Fabio wrote (2009-10-24 11:12:48):

> 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?

The obvious solution is to store dates and times as UTC in your
databases, and handle time zones dst in your application. I don't know
the nature of your application, but if you implement special treatmet
of 3A:00 and 3B:00 it is still of no use if your application is to be
run in some Brazilian locale where the change to and from dst happens
at 00:00/01:00 instead of 02:00/03:00 as in most countries.

Bernt

>
> Fabio

[Antony Sequeira]

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@...> 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
>