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 <[email protected]> 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 >
