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 20:01:39 UTC
[jira] Created: (DERBY-5065) timestampdiff doesn't differentiate
between 28 march 2010 and 29 march 2010
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 of 2010.
You can easily check this with this script in ijtools:
-- 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
[jira] Issue Comment Edited: (DERBY-5065) timestampdiff doesn't
differentiate between 28 march 2010 and 29 march 2010
Posted by "emmanuel chemla (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Closed] (DERBY-5065) timestampdiff doesn't differentiate
between 28 march 2010 and 29 march 2010
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5065?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas closed DERBY-5065.
--------------------------------
Resolution: Not A Problem
I am closing this issue as "Not a problem". I agree with Knut's analysis of dates which straddle clock shifts and I have verified this behavior.
The behavior of timestampdiff is incompletely specified here: http://msdn.microsoft.com/en-us/library/ms714639%28v=VS.85%29.aspx Note that rounding/truncation behavior is not defined. Some vendors round, some truncate. See http://shimansak-obiee.blogspot.com/2010/02/difference-between-two-dates.html
I believe that Derby's behavior falls within the tolerances of the spec. That behavior agrees with some databases and disagrees with others. I do not see any value in flipping that (dis)agreement.
> 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
[jira] Commented: (DERBY-5065) timestampdiff doesn't differentiate
between 28 march 2010 and 29 march 2010
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5065?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12998554#comment-12998554 ]
Knut Anders Hatlen commented on DERBY-5065:
-------------------------------------------
What happens here, is that the two date arguments are converted to timestamps with the time component set to 00:00:00. Because of daylight savings time, the difference between 2010-03-28 00:00:00 and 2010-03-29 00:00:00 is 23 hours, which is less than one day.
Since the arguments to the TIMESTAMPDIFF function are defined as timestamps, I'm not sure exactly what to expect when the actual arguments are dates.
> 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
[jira] Commented: (DERBY-5065) timestampdiff doesn't differentiate
between 28 march 2010 and 29 march 2010
Posted by "emmanuel chemla (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5065?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12998578#comment-12998578 ]
emmanuel chemla commented on DERBY-5065:
----------------------------------------
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
[jira] Updated: (DERBY-5065) timestampdiff doesn't differentiate
between 28 march 2010 and 29 march 2010
Posted by "emmanuel chemla (JIRA)" <ji...@apache.org>.
[ 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 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 !
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 ijtools:
-- 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 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 !
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Issue Comment Edited: (DERBY-5065) timestampdiff doesn't
differentiate between 28 march 2010 and 29 march 2010
Posted by "emmanuel chemla (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Commented] (DERBY-5065) timestampdiff doesn't differentiate
between 28 march 2010 and 29 march 2010
Posted by "emmanuel chemla (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5065?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13015074#comment-13015074 ]
emmanuel chemla commented on DERBY-5065:
----------------------------------------
Ok, at least, I believe that this behaviour should be documented, so that a
person using it like "datedif" to get amount of days, would be aware that he
could sometimes end up with "missing days" and hence, for such a use (i.e.
computation of simple days) a workaround is absolutely needed.
> 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
[jira] Updated: (DERBY-5065) timestampdiff doesn't differentiate
between 28 march 2010 and 29 march 2010
Posted by "emmanuel chemla (JIRA)" <ji...@apache.org>.
[ 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
[jira] Issue Comment Edited: (DERBY-5065) timestampdiff doesn't
differentiate between 28 march 2010 and 29 march 2010
Posted by "emmanuel chemla (JIRA)" <ji...@apache.org>.
[ 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:16 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/ending dates are in the opposite case (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 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