You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "emmanuel chemla (JIRA)" <ji...@apache.org> on 2011/02/23 23:16:38 UTC
[jira] Issue Comment Edited: (DERBY-5065) timestampdiff doesn't
differentiate between 28 march 2010 and 29 march 2010
[ 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/23/11 10:14 PM:
------------------------------------------------------------------
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 date in the opposite case (disregarding the number of years separating them)
was (Author: emchem):
The trouble is not solely on this specific dates Every days interval that overlaps this 28-29 (i.e. beginning date before 29 and ending date after 29) is defective.
So it's a serious problem. 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 date 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