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] Updated: (DERBY-5065) timestampdiff doesn't differentiate between 28 march 2010 and 29 march 2010
Date Wed, 23 Feb 2011 19:01:38 GMT

     [ 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 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  !


  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 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  !



> 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

        

Mime
View raw message