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.