db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "emmanuel chemla (JIRA)" <j...@apache.org>
Subject [jira] Issue Comment Edited: (DERBY-5065) timestampdiff doesn't differentiate between 28 march 2010 and 29 march 2010
Date Wed, 23 Feb 2011 22:16:38 GMT

    [ 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

        

Mime
View raw message