problem with timestampdiff

[emchem <em...@chem.la>]

Hi, I needed to accomplish a simple task with Derby: computing an interval of
days (like datedif in excel).

I used the TIMESTAMPDIFF function, it seemed to work well, until I noticed a
strange behaviour : sometimes the computation is OK, sometimes not.

After having searched the trouble, I've found that from TIMESTAMPDIFF's
point of view, the 28 march 2010 is not a different day than the 29 march of
2010.

You can easily check this with this script :

-- creating testing environment 
CREATE TABLE dummy (beginning DATE, ending DATE);
INSERT INTO dummy (beginning, ending) VALUES ('2010-04-01', '2010-04-01');
INSERT INTO dummy (beginning, ending) VALUES ('2010-03-31', '2010-04-01');
INSERT INTO dummy (beginning, ending) VALUES ('2010-03-30', '2010-04-01');
INSERT INTO dummy (beginning, ending) VALUES ('2010-03-29', '2010-04-01');
INSERT INTO dummy (beginning, ending) VALUES ('2010-03-28', '2010-04-01');
INSERT INTO dummy (beginning, ending) VALUES ('2010-03-27', '2010-04-01');
INSERT INTO dummy (beginning, ending) VALUES ('2010-03-26', '2010-04-01');
--
-- checking the odd behaviour
SELECT beginning, ending, {fn timestampdiff(SQL_TSI_DAY, beginning, ending
)} AS "interval in days" FROM dummy;
--
--  cleaning
DROP TABLE dummy


You'll get this result :

beginning	ending	interval in days
2010-04-01;	2010-04-01;	0
2010-03-31	;	2010-04-01;	1
2010-03-30	;	2010-04-01;	2
2010-03-29	;	2010-04-01;	3
2010-03-28	;	2010-04-01;	3
2010-03-27	;	2010-04-01;	4
2010-03-26	;	2010-04-01;	5

Someone has any insight on this ? Is there a nicer way to get the interval
in days that won't get into this trouble ?

-- 
View this message in context: http://old.nabble.com/problem-with-timestampdiff-tp30997315p30997315.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: problem with timestampdiff

[emmanuel chemla <em...@chem.la>]

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.

Re: problem with timestampdiff

[Knut Anders Hatlen <kn...@oracle.com>]

emchem <em...@chem.la> writes:

> Hi, I needed to accomplish a simple task with Derby: computing an interval of
> days (like datedif in excel).
>
> I used the TIMESTAMPDIFF function, it seemed to work well, until I noticed a
> strange behaviour : sometimes the computation is OK, sometimes not.
>
> After having searched the trouble, I've found that from TIMESTAMPDIFF's
> point of view, the 28 march 2010 is not a different day than the 29 march of
> 2010.

Hi,

This sounds like a bug. 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. It would be great if you could file a bug
report at https://issues.apache.org/jira/browse/DERBY so that we can
track the problem.

Thanks,
-- 
Knut Anders