[
https://issues.apache.org/jira/browse/DERBY-5065?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12998578#comment-12998578
]
emmanuel chemla edited comment on DERBY-5065 at 2/24/11 9:55 AM:
-----------------------------------------------------------------
The trouble is not solely on those specific dates (28 and 29 march 2010) but
this issue stands for every days' interval that overlaps this DST point in time.
In the example above: not only the "2010-03-28" line is mistaken but all the
lines below !!!
According to your hypothesis, we should expect to have a correct interval of
days, when both dates (beginning and ending) are in the same season :
( I mean that there won't be any trouble if we compute interval on, e.g.
2010-04-01 and 2007-04-01).
Still building on your hypothesis, if we compute interval on dates pertaining
to different seasons, there will be a missing day when beginning date is in
winter and ending day in summer, disregarding the number of years separating
them.
was (Author: emchem):
The trouble is not solely on those specific dates (28 and 29 march 2010)
but this issue stands for every days' interval that overlaps this DST point in
time.
In the example above: not only the "2010-03-28" line is mistaken but all the
lines below !!!
According to your hypothesis, we should expect to have a correct interval of
days, when both dates (beginning and ending) are in the same season :
( I mean that there won't be any trouble if we compute interval on, e.g.
2010-04-01 and 2007-04-01).
Still building on your hypothesis, if we compute interval on dates pertaining
to different seasons :
- there will be a missing day when beginning date is in winter and ending day
in summer (disregarding the number of years separating them)
- there will be a bonus day when beginning/ending dates are in the opposite
case (disregarding the number of years separating them)
> 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
