db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From emmanuel chemla <emman...@chem.la>
Subject Re: problem with timestampdiff
Date Thu, 24 Feb 2011 00:48:39 GMT

Knut Anders Hatlen-5 wrote:
> The problematic date seems to coincide with the
> switch to daylight saving time in Europe, so my guess would be that it's
> somehow related to that.

Ok, with your hint, I've tried this workaround, successfully:
In order to calculate correctly the difference between two dates, in the
example above, you must modify the query like this:
SELECT beginning, ending, {fn timestampdiff(SQL_TSI_DAY, beginning ,
timestamp(ending || ' 01:00:00'))} AS "interval in days" FROM dummy; 

Here's the explanation of the workaround : 
Knut Anders Hatlen pointed out that, when using pure date format, an hour is
"lost" when passing from standard time (aka "winter time") to daylight
saving time (aka "summer time"). 
Why ?
Because, pure date format are translated to datetime format by adding the
00:00:00 hour the date.
This is how an hour get lost :
the real difference between '2010-04-29 00:00:00' and '2010-04-28 00:00:00'
is not truly 24 hours but 23 hours. (remember : the DST thing means one less
sleep hour). So the true difference is in fact less than one day. so adding
an hour (or two, or three ...) will correct the formula.
View this message in context: http://old.nabble.com/problem-with-timestampdiff-tp30997315p31000295.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

View raw message