Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of pruefsum@me.com designates
 17.148.16.101 as permitted sender)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; charset=us-ascii; format=flowed; delsp=yes
From: Fabio <pruefsum@me.com>
Subject: time format (or: the curse of dst)
Date: Sat, 24 Oct 2009 11:12:48 +0200
Message-id: <FCE5300B-7F78-42BB-81EF-0D79D0F5B9FF@me.com>
To: derby-user@db.apache.org

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