Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 82566 invoked from network); 23 Feb 2011 19:02:04 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 23 Feb 2011 19:02:04 -0000 Received: (qmail 26022 invoked by uid 500); 23 Feb 2011 19:02:04 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 25764 invoked by uid 500); 23 Feb 2011 19:02:01 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 25744 invoked by uid 99); 23 Feb 2011 19:01:59 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 23 Feb 2011 19:01:59 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED,T_RP_MATCHES_RCVD X-Spam-Check-By: apache.org Received: from [140.211.11.116] (HELO hel.zones.apache.org) (140.211.11.116) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 23 Feb 2011 19:01:59 +0000 Received: from hel.zones.apache.org (hel.zones.apache.org [140.211.11.116]) by hel.zones.apache.org (Postfix) with ESMTP id EC0261B34DB for ; Wed, 23 Feb 2011 19:01:38 +0000 (UTC) Date: Wed, 23 Feb 2011 19:01:38 +0000 (UTC) From: "emmanuel chemla (JIRA)" To: derby-dev@db.apache.org Message-ID: <1758358254.11609.1298487698963.JavaMail.tomcat@hel.zones.apache.org> Subject: [jira] Updated: (DERBY-5065) timestampdiff doesn't differentiate between 28 march 2010 and 29 march 2010 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ 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