[
https://issues.apache.org/jira/browse/DERBY-5065?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
emmanuel chemla updated DERBY-5065:
-----------------------------------
Description:
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 2010.
You can easily check it with this script in ij tool :
-- creating testing environment
CONNECT 'jdbc:derby:MyDbTestxyz;create=true';
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 !
was:
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 2010.
You can easily check this with this script in ij tool :
-- creating testing environment
CONNECT 'jdbc:derby:MyDbTestxyz;create=true';
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 !
> timestampdiff doesn't differentiate between 28 march 2010 and 29 march 2010
> ---------------------------------------------------------------------------
>
> Key: DERBY-5065
> URL: https://issues.apache.org/jira/browse/DERBY-5065
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.7.1.1
> Environment: windows 7, the bug has been testified using ij tool and
> usign squirrel sql. My locales are "France"
> Reporter: emmanuel chemla
>
> 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 2010.
> You can easily check it with this script in ij tool :
> -- creating testing environment
> CONNECT 'jdbc:derby:MyDbTestxyz;create=true';
> 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 !
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
