From: Daniel S. <dan...@oc...> - 2012-10-26 11:34:21
|
Hi All, I've been using HSQLDB as a test environment with an exclusively Hibernate application for a long time and it works fantastically. I'm now working on an application which uses raw SQL calls only (for clarity, robustness and reliability reasons this won't be changing, nor am I being allowed by my managers to change any of the SQL itself), as such i've encountered a couple of peculiarities around dates and have no idea how to get around them. We use Oracle as our normal database, hence I have set the ora_syntax variable = true (which I have confirmed, select * from dual works fine). I have tried HSQLDB 2.2.8, 2.2.9, 2.0.1 and all have these styles of peculiarities. Peculiarity number 1: select sysdate + 5 from dual - returns what I as an Oracle user expect - 5 days + today select sysdate - trunc(sysdate) from dual - returns the difference in seconds. This differs from Oracle, where it would return the result as days. Is there a default that is used to translate the date subtraction into using the DATEDIFF function which is choosing seconds, where perhaps in ora_syntax mode it should be using DAYS? Even if its not a setting, could anyone direct me to perhaps which class in the hsqldb library I could look at to modify to change the default from SECONDS to DAYS, as I don't mind having a bit of a modified version of the hsqldb library sitting in our artifactory. Peculiarity number 2: select sysdate - trunc(sysdate) from dual returns a number (Integer - number of seconds - ignoring peculiarity number 1 - this is just a number) select case when 1>2 then sysdate - trunc(sysdate) else 999 from dual This returns an error - "Caused by: org.hsqldb.HsqlException: incompatible data types in combination". Now I have read that in reality HSQLDB is returning a 'datetime interval' object from the sysdate-trunc(sysdate) - and it is only because JDBC cannot support it that it is translated into a numeric value at the end - but this is making it really difficult to test this style of function. Since the HSQLDB is being used for tests, and the application will run on Oracle when it is live, is there any way to have a syntax of SQL supported by both to complete this style of case statement? Thanks kindly, Daniel Stoner This message has been checked for all known viruses by the Postini Virus Control Centre. |
From: Fred T. <fr...@us...> - 2012-10-26 12:38:51
|
For common syntax between HSQLDB and Oracle, do not use the shortcuts you are using in Oracle. SELECT SYSDATE + 5 DAY should be used in the first example. SELECT (SYSDATE - TRUNC(SYSDATE) ) DAY -- but this looks like a mistake as the result is always zero SELECT CASE WHEN 1>2 THEN (SYSDATE - TRUNC(SYSDATE) ) HOUR ELSE 999 HOUR END FROM DUAL See the Guide on treatment of DATETIME and INTERVAL types. Fred On Fri, Oct 26, 2012, at 12:05, Daniel Stoner wrote: Hi All, I've been using HSQLDB as a test environment with an exclusively Hibernate application for a long time and it works fantastically. I'm now working on an application which uses raw SQL calls only (for clarity, robustness and reliability reasons this won't be changing, nor am I being allowed by my managers to change any of the SQL itself), as such i've encountered a couple of peculiarities around dates and have no idea how to get around them. We use Oracle as our normal database, hence I have set the ora_syntax variable = true (which I have confirmed, select * from dual works fine). I have tried HSQLDB 2.2.8, 2.2.9, 2.0.1 and all have these styles of peculiarities. Peculiarity number 1: select sysdate + 5 from dual - returns what I as an Oracle user expect - 5 days + today select sysdate - trunc(sysdate) from dual - returns the difference in seconds. This differs from Oracle, where it would return the result as days. Is there a default that is used to translate the date subtraction into using the DATEDIFF function which is choosing seconds, where perhaps in ora_syntax mode it should be using DAYS? Even if its not a setting, could anyone direct me to perhaps which class in the hsqldb library I could look at to modify to change the default from SECONDS to DAYS, as I don't mind having a bit of a modified version of the hsqldb library sitting in our artifactory. Peculiarity number 2: select sysdate - trunc(sysdate) from dual returns a number (Integer - number of seconds - ignoring peculiarity number 1 - this is just a number) select case when 1>2 then sysdate - trunc(sysdate) else 999 from dual This returns an error - "Caused by: org.hsqldb.HsqlException: incompatible data types in combination". Now I have read that in reality HSQLDB is returning a 'datetime interval' object from the sysdate-trunc(sysdate) - and it is only because JDBC cannot support it that it is translated into a numeric value at the end - but this is making it really difficult to test this style of function. Since the HSQLDB is being used for tests, and the application will run on Oracle when it is live, is there any way to have a syntax of SQL supported by both to complete this style of case statement? Thanks kindly, Daniel Stoner This message has been checked for all known viruses by the Postini Virus Control Centre. ----------------------------------------------------------------------- ------- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: [1]http://p.sf.net/sfu/appdyn_sfd2d_oct _______________________________________________ Hsqldb-user mailing list [2]Hsq...@li... [3]https://lists.sourceforge.net/lists/listinfo/hsqldb-user References 1. http://p.sf.net/sfu/appdyn_sfd2d_oct 2. mailto:Hsq...@li... 3. https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
From: Daniel S. <dan...@oc...> - 2012-10-26 12:39:12
|
I have found a workaround for peculiarity number 2: select case when 1>2 then cast(sysdate - trunc(sysdate) as NUMBER) else 999 from dual This works on Oracle and HSQLDB. I still however have a major issue with the default result of date subtraction being returned in seconds, as opposed to days. I note the documentation on SQL-92 infers that the result should be the lowest common utilised denominator of the two date datatypes - in this case, you would expect trunc(sysdate) - (trunc(sysdate)+1) to return -1 days since there was no time component, but this is not the result I am seeing in HSQLDB. Any assistance with this would be hugely appreciated. Thanks kindly, Daniel Stoner On 26 October 2012 12:05, Daniel Stoner <dan...@oc...> wrote: > Hi All, > > I've been using HSQLDB as a test environment with an exclusively Hibernate > application for a long time and it works fantastically. > > I'm now working on an application which uses raw SQL calls only (for > clarity, robustness and reliability reasons this won't be changing, nor am > I being allowed by my managers to change any of the SQL itself), as such > i've encountered a couple of peculiarities around dates and have no idea > how to get around them. > > We use Oracle as our normal database, hence I have set the ora_syntax > variable = true (which I have confirmed, select * from dual works fine). > > I have tried HSQLDB 2.2.8, 2.2.9, 2.0.1 and all have these styles of > peculiarities. > > Peculiarity number 1: > select sysdate + 5 from dual - returns what I as an Oracle user expect - 5 > days + today > > select sysdate - trunc(sysdate) from dual - returns the difference in > seconds. This differs from Oracle, where it would return the result as days. > > Is there a default that is used to translate the date subtraction into > using the DATEDIFF function which is choosing seconds, where perhaps in > ora_syntax mode it should be using DAYS? > > Even if its not a setting, could anyone direct me to perhaps which class > in the hsqldb library I could look at to modify to change the default from > SECONDS to DAYS, as I don't mind having a bit of a modified version of the > hsqldb library sitting in our artifactory. > > > > Peculiarity number 2: > select sysdate - trunc(sysdate) from dual returns a number (Integer - > number of seconds - ignoring peculiarity number 1 - this is just a number) > > select case when 1>2 then sysdate - trunc(sysdate) else 999 from dual > > This returns an error - "Caused by: org.hsqldb.HsqlException: incompatible > data types in combination". > > Now I have read that in reality HSQLDB is returning a 'datetime interval' > object from the sysdate-trunc(sysdate) - and it is only because JDBC cannot > support it that it is translated into a numeric value at the end - but this > is making it really difficult to test this style of function. > > > Since the HSQLDB is being used for tests, and the application will run on > Oracle when it is live, is there any way to have a syntax of SQL supported > by both to complete this style of case statement? > > > Thanks kindly, > Daniel Stoner > This message has been checked for all known viruses by the Postini Virus Control Centre. |
From: Daniel S. <dan...@oc...> - 2012-10-26 12:42:35
|
Hi Fred, Thanks very much for the response. No mistake, I just wanted to give a simple example of the peculiarity: select (sysdate - trunc(sysdate)) DAY from dual Is not valid syntax in Oracle (I am using 10g but I have also tested on 11g and it is still not valid). Thanks kindly, Daniel Stoner On 26 October 2012 13:38, Fred Toussi <fr...@us...> wrote: > For common syntax between HSQLDB and Oracle, do not use the shortcuts > you are using in Oracle. > > SELECT SYSDATE + 5 DAY should be used in the first example. > > SELECT (SYSDATE - TRUNC(SYSDATE) ) DAY -- but this looks like a mistake > as the result is always zero > > SELECT CASE WHEN 1>2 THEN (SYSDATE - TRUNC(SYSDATE) ) HOUR ELSE 999 HOUR > END FROM DUAL > > See the Guide on treatment of DATETIME and INTERVAL types. > > Fred > > On Fri, Oct 26, 2012, at 12:05, Daniel Stoner wrote: > > Hi All, > > I've been using HSQLDB as a test environment with an exclusively > Hibernate application for a long time and it works fantastically. > > I'm now working on an application which uses raw SQL calls only (for > clarity, robustness and reliability reasons this won't be changing, nor am > I being allowed by my managers to change any of the SQL itself), as such > i've encountered a couple of peculiarities around dates and have no idea > how to get around them. > > We use Oracle as our normal database, hence I have set the ora_syntax > variable = true (which I have confirmed, select * from dual works fine). > > I have tried HSQLDB 2.2.8, 2.2.9, 2.0.1 and all have these styles of > peculiarities. > > Peculiarity number 1: > select sysdate + 5 from dual - returns what I as an Oracle user expect - > 5 days + today > > select sysdate - trunc(sysdate) from dual - returns the difference in > seconds. This differs from Oracle, where it would return the result as days. > > Is there a default that is used to translate the date subtraction into > using the DATEDIFF function which is choosing seconds, where perhaps in > ora_syntax mode it should be using DAYS? > > Even if its not a setting, could anyone direct me to perhaps which class > in the hsqldb library I could look at to modify to change the default from > SECONDS to DAYS, as I don't mind having a bit of a modified version of the > hsqldb library sitting in our artifactory. > > > > Peculiarity number 2: > select sysdate - trunc(sysdate) from dual returns a number (Integer - > number of seconds - ignoring peculiarity number 1 - this is just a number) > > select case when 1>2 then sysdate - trunc(sysdate) else 999 from dual > > This returns an error - "Caused by: org.hsqldb.HsqlException: > incompatible data types in combination". > > Now I have read that in reality HSQLDB is returning a 'datetime interval' > object from the sysdate-trunc(sysdate) - and it is only because JDBC cannot > support it that it is translated into a numeric value at the end - but this > is making it really difficult to test this style of function. > > > Since the HSQLDB is being used for tests, and the application will run on > Oracle when it is live, is there any way to have a syntax of SQL supported > by both to complete this style of case statement? > > > Thanks kindly, > Daniel Stoner > > This message has been checked for all known viruses by the Postini Virus Control Centre. > > > ------------------------------------------------------------------------------ > Everyone hates slow websites. So do we. > Make your web apps faster with AppDynamics > Download AppDynamics Lite for free today: > http://p.sf.net/sfu/appdyn_sfd2d_oct > *_______________________________________________* > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > > > > ------------------------------------------------------------------------------ > Everyone hates slow websites. So do we. > Make your web apps faster with AppDynamics > Download AppDynamics Lite for free today: > http://p.sf.net/sfu/appdyn_sfd2d_oct > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > This message has been checked for all known viruses by the Postini Virus Control Centre. |
From: Daniel S. <dan...@oc...> - 2012-10-26 13:01:21
|
To be entirely accurate: select (sysdate - trunc(sysdate)) from dual Actually returns the 'fraction of a day' IE seconds/(seconds in day) in Oracle. IE if I run this right now at 13:58 it returns ~0.583. I can live with the answer being returned in whole days (IE 0 in this case) as I suspect that would be easier, but obviously the perfect situation would be to have exactly the same result. Any pointer towards where in the code I could modify the behaviour would be fantastic, as I am getting the feeling that doing this in a way that would keep the SQL syntax compatible with both HSQLDB and Oracle is going to end up with something horrendous. Thanks kindly, Daniel Stoner On 26 October 2012 13:42, Daniel Stoner <dan...@oc...> wrote: > Hi Fred, > > Thanks very much for the response. > > No mistake, I just wanted to give a simple example of the peculiarity: > > select (sysdate - trunc(sysdate)) DAY from dual > Is not valid syntax in Oracle (I am using 10g but I have also tested on > 11g and it is still not valid). > > Thanks kindly, > Daniel Stoner > > > On 26 October 2012 13:38, Fred Toussi <fr...@us...> wrote: > >> For common syntax between HSQLDB and Oracle, do not use the shortcuts >> you are using in Oracle. >> >> SELECT SYSDATE + 5 DAY should be used in the first example. >> >> SELECT (SYSDATE - TRUNC(SYSDATE) ) DAY -- but this looks like a mistake >> as the result is always zero >> >> SELECT CASE WHEN 1>2 THEN (SYSDATE - TRUNC(SYSDATE) ) HOUR ELSE 999 HOUR >> END FROM DUAL >> >> See the Guide on treatment of DATETIME and INTERVAL types. >> >> Fred >> >> On Fri, Oct 26, 2012, at 12:05, Daniel Stoner wrote: >> >> Hi All, >> >> I've been using HSQLDB as a test environment with an exclusively >> Hibernate application for a long time and it works fantastically. >> >> I'm now working on an application which uses raw SQL calls only (for >> clarity, robustness and reliability reasons this won't be changing, nor am >> I being allowed by my managers to change any of the SQL itself), as such >> i've encountered a couple of peculiarities around dates and have no idea >> how to get around them. >> >> We use Oracle as our normal database, hence I have set the ora_syntax >> variable = true (which I have confirmed, select * from dual works fine). >> >> I have tried HSQLDB 2.2.8, 2.2.9, 2.0.1 and all have these styles of >> peculiarities. >> >> Peculiarity number 1: >> select sysdate + 5 from dual - returns what I as an Oracle user expect - >> 5 days + today >> >> select sysdate - trunc(sysdate) from dual - returns the difference in >> seconds. This differs from Oracle, where it would return the result as days. >> >> Is there a default that is used to translate the date subtraction into >> using the DATEDIFF function which is choosing seconds, where perhaps in >> ora_syntax mode it should be using DAYS? >> >> Even if its not a setting, could anyone direct me to perhaps which class >> in the hsqldb library I could look at to modify to change the default from >> SECONDS to DAYS, as I don't mind having a bit of a modified version of the >> hsqldb library sitting in our artifactory. >> >> >> >> Peculiarity number 2: >> select sysdate - trunc(sysdate) from dual returns a number (Integer - >> number of seconds - ignoring peculiarity number 1 - this is just a number) >> >> select case when 1>2 then sysdate - trunc(sysdate) else 999 from dual >> >> This returns an error - "Caused by: org.hsqldb.HsqlException: >> incompatible data types in combination". >> >> Now I have read that in reality HSQLDB is returning a 'datetime >> interval' object from the sysdate-trunc(sysdate) - and it is only because >> JDBC cannot support it that it is translated into a numeric value at the >> end - but this is making it really difficult to test this style of function. >> >> >> Since the HSQLDB is being used for tests, and the application will run >> on Oracle when it is live, is there any way to have a syntax of SQL >> supported by both to complete this style of case statement? >> >> >> Thanks kindly, >> Daniel Stoner >> >> This message has been checked for all known viruses by the Postini Virus Control Centre. >> >> >> ------------------------------------------------------------------------------ >> Everyone hates slow websites. So do we. >> Make your web apps faster with AppDynamics >> Download AppDynamics Lite for free today: >> http://p.sf.net/sfu/appdyn_sfd2d_oct >> *_______________________________________________* >> Hsqldb-user mailing list >> Hsq...@li... >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> >> >> >> >> ------------------------------------------------------------------------------ >> Everyone hates slow websites. So do we. >> Make your web apps faster with AppDynamics >> Download AppDynamics Lite for free today: >> http://p.sf.net/sfu/appdyn_sfd2d_oct >> _______________________________________________ >> Hsqldb-user mailing list >> Hsq...@li... >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> >> > This message has been checked for all known viruses by the Postini Virus Control Centre. |
From: Fred T. <fr...@us...> - 2012-10-26 15:30:07
|
Please explain what you want to achieve and provide a more real example. Fred On Fri, Oct 26, 2012, at 14:01, Daniel Stoner wrote: To be entirely accurate: select (sysdate - trunc(sysdate)) from dual Actually returns the 'fraction of a day' IE seconds/(seconds in day) in Oracle. IE if I run this right now at 13:58 it returns ~0.583. I can live with the answer being returned in whole days (IE 0 in this case) as I suspect that would be easier, but obviously the perfect situation would be to have exactly the same result. Any pointer towards where in the code I could modify the behaviour would be fantastic, as I am getting the feeling that doing this in a way that would keep the SQL syntax compatible with both HSQLDB and Oracle is going to end up with something horrendous. Thanks kindly, Daniel Stoner On 26 October 2012 13:42, Daniel Stoner <[1]dan...@oc...> wrote: Hi Fred, Thanks very much for the response. No mistake, I just wanted to give a simple example of the peculiarity: select (sysdate - trunc(sysdate)) DAY from dual Is not valid syntax in Oracle (I am using 10g but I have also tested on 11g and it is still not valid). Thanks kindly, Daniel Stoner On 26 October 2012 13:38, Fred Toussi <[2]fr...@us...> wrote: For common syntax between HSQLDB and Oracle, do not use the shortcuts you are using in Oracle. SELECT SYSDATE + 5 DAY should be used in the first example. SELECT (SYSDATE - TRUNC(SYSDATE) ) DAY -- but this looks like a mistake as the result is always zero SELECT CASE WHEN 1>2 THEN (SYSDATE - TRUNC(SYSDATE) ) HOUR ELSE 999 HOUR END FROM DUAL See the Guide on treatment of DATETIME and INTERVAL types. Fred On Fri, Oct 26, 2012, at 12:05, Daniel Stoner wrote: Hi All, I've been using HSQLDB as a test environment with an exclusively Hibernate application for a long time and it works fantastically. I'm now working on an application which uses raw SQL calls only (for clarity, robustness and reliability reasons this won't be changing, nor am I being allowed by my managers to change any of the SQL itself), as such i've encountered a couple of peculiarities around dates and have no idea how to get around them. We use Oracle as our normal database, hence I have set the ora_syntax variable = true (which I have confirmed, select * from dual works fine). I have tried HSQLDB 2.2.8, 2.2.9, 2.0.1 and all have these styles of peculiarities. Peculiarity number 1: select sysdate + 5 from dual - returns what I as an Oracle user expect - 5 days + today select sysdate - trunc(sysdate) from dual - returns the difference in seconds. This differs from Oracle, where it would return the result as days. Is there a default that is used to translate the date subtraction into using the DATEDIFF function which is choosing seconds, where perhaps in ora_syntax mode it should be using DAYS? Even if its not a setting, could anyone direct me to perhaps which class in the hsqldb library I could look at to modify to change the default from SECONDS to DAYS, as I don't mind having a bit of a modified version of the hsqldb library sitting in our artifactory. Peculiarity number 2: select sysdate - trunc(sysdate) from dual returns a number (Integer - number of seconds - ignoring peculiarity number 1 - this is just a number) select case when 1>2 then sysdate - trunc(sysdate) else 999 from dual This returns an error - "Caused by: org.hsqldb.HsqlException: incompatible data types in combination". Now I have read that in reality HSQLDB is returning a 'datetime interval' object from the sysdate-trunc(sysdate) - and it is only because JDBC cannot support it that it is translated into a numeric value at the end - but this is making it really difficult to test this style of function. Since the HSQLDB is being used for tests, and the application will run on Oracle when it is live, is there any way to have a syntax of SQL supported by both to complete this style of case statement? Thanks kindly, Daniel Stoner This message has been checked for all known viruses by the Postini Virus Control Centre. ----------------------------------------------------------------------- ------- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: [3]http://p.sf.net/sfu/appdyn_sfd2d_oct _______________________________________________ Hsqldb-user mailing list [4]Hsq...@li... [5]https://lists.sourceforge.net/lists/listinfo/hsqldb-user -------------------------------------------------------------------- ---------- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: [6]http://p.sf.net/sfu/appdyn_sfd2d_oct _______________________________________________ Hsqldb-user mailing list [7]Hsq...@li... [8]https://lists.sourceforge.net/lists/listinfo/hsqldb-user This message has been checked for all known viruses by the Postini Virus Control Centre. ----------------------------------------------------------------------- ------- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: [9]http://p.sf.net/sfu/appdyn_sfd2d_oct _______________________________________________ Hsqldb-user mailing list [10]Hsq...@li... [11]https://lists.sourceforge.net/lists/listinfo/hsqldb-user References 1. mailto:dan...@oc... 2. mailto:fr...@us... 3. http://p.sf.net/sfu/appdyn_sfd2d_oct 4. mailto:Hsq...@li... 5. https://lists.sourceforge.net/lists/listinfo/hsqldb-user 6. http://p.sf.net/sfu/appdyn_sfd2d_oct 7. mailto:Hsq...@li... 8. https://lists.sourceforge.net/lists/listinfo/hsqldb-user 9. http://p.sf.net/sfu/appdyn_sfd2d_oct 10. mailto:Hsq...@li... 11. https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
From: Daniel S. <dan...@oc...> - 2012-10-26 15:38:55
|
Hi Fred, As in the original example: select (sysdate - trunc(sysdate)) from dual In ora_syntax=true mode, this should return 0 (Days) and thus be consistent with other date arithmetic [IE sysdate+5 is interpreted as now+5days for instance]. It is currently returning <LARGE NUMBER> (Seconds). If we lived in a perfect world and we matched Oracle behaviour perfectly, i'd ask for it to return 0.1234 (Fraction of Days) but I suspect that's beyond what is easily possible. Adding the DAY interval to the query is not valid Oracle syntax, and thus not a possible solution to this. I'm not quite sure how a more 'real' example will help, but if you need one - then I have a table with a date column in it, and I want to know how many days away from trunc(sysdate) those days are. Thanks kindly, Daniel Stoner On 26 October 2012 16:29, Fred Toussi <fr...@us...> wrote: > Please explain what you want to achieve and provide a more real example. > > Fred > > On Fri, Oct 26, 2012, at 14:01, Daniel Stoner wrote: > > To be entirely accurate: > > select (sysdate - trunc(sysdate)) from dual > Actually returns the 'fraction of a day' IE seconds/(seconds in day) in > Oracle. IE if I run this right now at 13:58 it returns ~0.583. > > I can live with the answer being returned in whole days (IE 0 in this > case) as I suspect that would be easier, but obviously the perfect > situation would be to have exactly the same result. > > Any pointer towards where in the code I could modify the behaviour would > be fantastic, as I am getting the feeling that doing this in a way that > would keep the SQL syntax compatible with both HSQLDB and Oracle is going > to end up with something horrendous. > > Thanks kindly, > Daniel Stoner > > On 26 October 2012 13:42, Daniel Stoner <dan...@oc...> wrote: > > Hi Fred, > > Thanks very much for the response. > > No mistake, I just wanted to give a simple example of the peculiarity: > > select (sysdate - trunc(sysdate)) DAY from dual > Is not valid syntax in Oracle (I am using 10g but I have also tested on > 11g and it is still not valid). > > Thanks kindly, > Daniel Stoner > > > On 26 October 2012 13:38, Fred Toussi <fr...@us...>wrote: > > For common syntax between HSQLDB and Oracle, do not use the shortcuts > you are using in Oracle. > > SELECT SYSDATE + 5 DAY should be used in the first example. > > SELECT (SYSDATE - TRUNC(SYSDATE) ) DAY -- but this looks like a mistake > as the result is always zero > > SELECT CASE WHEN 1>2 THEN (SYSDATE - TRUNC(SYSDATE) ) HOUR ELSE 999 HOUR > END FROM DUAL > > See the Guide on treatment of DATETIME and INTERVAL types. > > Fred > > On Fri, Oct 26, 2012, at 12:05, Daniel Stoner wrote: > > Hi All, > > I've been using HSQLDB as a test environment with an exclusively > Hibernate application for a long time and it works fantastically. > > I'm now working on an application which uses raw SQL calls only (for > clarity, robustness and reliability reasons this won't be changing, nor am > I being allowed by my managers to change any of the SQL itself), as such > i've encountered a couple of peculiarities around dates and have no idea > how to get around them. > > We use Oracle as our normal database, hence I have set the ora_syntax > variable = true (which I have confirmed, select * from dual works fine). > > I have tried HSQLDB 2.2.8, 2.2.9, 2.0.1 and all have these styles of > peculiarities. > > Peculiarity number 1: > select sysdate + 5 from dual - returns what I as an Oracle user expect - > 5 days + today > > select sysdate - trunc(sysdate) from dual - returns the difference in > seconds. This differs from Oracle, where it would return the result as days. > > Is there a default that is used to translate the date subtraction into > using the DATEDIFF function which is choosing seconds, where perhaps in > ora_syntax mode it should be using DAYS? > > Even if its not a setting, could anyone direct me to perhaps which class > in the hsqldb library I could look at to modify to change the default from > SECONDS to DAYS, as I don't mind having a bit of a modified version of the > hsqldb library sitting in our artifactory. > > > > Peculiarity number 2: > select sysdate - trunc(sysdate) from dual returns a number (Integer - > number of seconds - ignoring peculiarity number 1 - this is just a number) > > select case when 1>2 then sysdate - trunc(sysdate) else 999 from dual > > This returns an error - "Caused by: org.hsqldb.HsqlException: > incompatible data types in combination". > > Now I have read that in reality HSQLDB is returning a 'datetime interval' > object from the sysdate-trunc(sysdate) - and it is only because JDBC cannot > support it that it is translated into a numeric value at the end - but this > is making it really difficult to test this style of function. > > > Since the HSQLDB is being used for tests, and the application will run on > Oracle when it is live, is there any way to have a syntax of SQL supported > by both to complete this style of case statement? > > > Thanks kindly, > Daniel Stoner > > This message has been checked for all known viruses by the Postini Virus Control Centre. > > > ------------------------------------------------------------------------------ > Everyone hates slow websites. So do we. > Make your web apps faster with AppDynamics > Download AppDynamics Lite for free today: > http://p.sf.net/sfu/appdyn_sfd2d_oct > *_______________________________________________* > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > > > > ------------------------------------------------------------------------------ > Everyone hates slow websites. So do we. > Make your web apps faster with AppDynamics > Download AppDynamics Lite for free today: > http://p.sf.net/sfu/appdyn_sfd2d_oct > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > > This message has been checked for all known viruses by the Postini Virus Control Centre. > > > ------------------------------------------------------------------------------ > Everyone hates slow websites. So do we. > Make your web apps faster with AppDynamics > Download AppDynamics Lite for free today: > http://p.sf.net/sfu/appdyn_sfd2d_oct > *_______________________________________________* > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > > > > ------------------------------------------------------------------------------ > The Windows 8 Center > In partnership with Sourceforge > Your idea - your app - 30 days. Get started! > http://windows8center.sourceforge.net/ > what-html-developers-need-to-know-about-coding-windows-8-metro-style-apps/ > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > This message has been checked for all known viruses by the Postini Virus Control Centre. |
From: Fred T. <fr...@us...> - 2012-10-26 16:16:24
|
Perhaps this works in Oracle: select cast(sysdate - trunc(sysdate) as interval day) from dual Although Oracle supports the INTERVAL types, it apparently doesn't support the sql standard date arithmetic fully, as used in the previous solution. As SYSDATE returns a timestamp with second resolution, HSQLDB returns an INTERVAL SECOND value for the subtraction. In future versions, we may be able to cover these peculiarities better in the ORA syntax mode. Fred On Fri, Oct 26, 2012, at 16:38, Daniel Stoner wrote: Hi Fred, As in the original example: select (sysdate - trunc(sysdate)) from dual In ora_syntax=true mode, this should return 0 (Days) and thus be consistent with other date arithmetic [IE sysdate+5 is interpreted as now+5days for instance]. It is currently returning <LARGE NUMBER> (Seconds). If we lived in a perfect world and we matched Oracle behaviour perfectly, i'd ask for it to return 0.1234 (Fraction of Days) but I suspect that's beyond what is easily possible. Adding the DAY interval to the query is not valid Oracle syntax, and thus not a possible solution to this. I'm not quite sure how a more 'real' example will help, but if you need one - then I have a table with a date column in it, and I want to know how many days away from trunc(sysdate) those days are. Thanks kindly, Daniel Stoner On 26 October 2012 16:29, Fred Toussi <[1]fr...@us...> wrote: Please explain what you want to achieve and provide a more real example. Fred On Fri, Oct 26, 2012, at 14:01, Daniel Stoner wrote: To be entirely accurate: select (sysdate - trunc(sysdate)) from dual Actually returns the 'fraction of a day' IE seconds/(seconds in day) in Oracle. IE if I run this right now at 13:58 it returns ~0.583. I can live with the answer being returned in whole days (IE 0 in this case) as I suspect that would be easier, but obviously the perfect situation would be to have exactly the same result. Any pointer towards where in the code I could modify the behaviour would be fantastic, as I am getting the feeling that doing this in a way that would keep the SQL syntax compatible with both HSQLDB and Oracle is going to end up with something horrendous. Thanks kindly, Daniel Stoner On 26 October 2012 13:42, Daniel Stoner <[2]dan...@oc...> wrote: Hi Fred, Thanks very much for the response. No mistake, I just wanted to give a simple example of the peculiarity: select (sysdate - trunc(sysdate)) DAY from dual Is not valid syntax in Oracle (I am using 10g but I have also tested on 11g and it is still not valid). Thanks kindly, Daniel Stoner On 26 October 2012 13:38, Fred Toussi <[3]fr...@us...> wrote: For common syntax between HSQLDB and Oracle, do not use the shortcuts you are using in Oracle. SELECT SYSDATE + 5 DAY should be used in the first example. SELECT (SYSDATE - TRUNC(SYSDATE) ) DAY -- but this looks like a mistake as the result is always zero SELECT CASE WHEN 1>2 THEN (SYSDATE - TRUNC(SYSDATE) ) HOUR ELSE 999 HOUR END FROM DUAL See the Guide on treatment of DATETIME and INTERVAL types. Fred On Fri, Oct 26, 2012, at 12:05, Daniel Stoner wrote: Hi All, I've been using HSQLDB as a test environment with an exclusively Hibernate application for a long time and it works fantastically. I'm now working on an application which uses raw SQL calls only (for clarity, robustness and reliability reasons this won't be changing, nor am I being allowed by my managers to change any of the SQL itself), as such i've encountered a couple of peculiarities around dates and have no idea how to get around them. We use Oracle as our normal database, hence I have set the ora_syntax variable = true (which I have confirmed, select * from dual works fine). I have tried HSQLDB 2.2.8, 2.2.9, 2.0.1 and all have these styles of peculiarities. Peculiarity number 1: select sysdate + 5 from dual - returns what I as an Oracle user expect - 5 days + today select sysdate - trunc(sysdate) from dual - returns the difference in seconds. This differs from Oracle, where it would return the result as days. Is there a default that is used to translate the date subtraction into using the DATEDIFF function which is choosing seconds, where perhaps in ora_syntax mode it should be using DAYS? Even if its not a setting, could anyone direct me to perhaps which class in the hsqldb library I could look at to modify to change the default from SECONDS to DAYS, as I don't mind having a bit of a modified version of the hsqldb library sitting in our artifactory. Peculiarity number 2: select sysdate - trunc(sysdate) from dual returns a number (Integer - number of seconds - ignoring peculiarity number 1 - this is just a number) select case when 1>2 then sysdate - trunc(sysdate) else 999 from dual This returns an error - "Caused by: org.hsqldb.HsqlException: incompatible data types in combination". Now I have read that in reality HSQLDB is returning a 'datetime interval' object from the sysdate-trunc(sysdate) - and it is only because JDBC cannot support it that it is translated into a numeric value at the end - but this is making it really difficult to test this style of function. Since the HSQLDB is being used for tests, and the application will run on Oracle when it is live, is there any way to have a syntax of SQL supported by both to complete this style of case statement? Thanks kindly, Daniel Stoner This message has been checked for all known viruses by the Postini Virus Control Centre. ----------------------------------------------------------------------- ------- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: [4]http://p.sf.net/sfu/appdyn_sfd2d_oct _______________________________________________ Hsqldb-user mailing list [5]Hsq...@li... [6]https://lists.sourceforge.net/lists/listinfo/hsqldb-user -------------------------------------------------------------------- ---------- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: [7]http://p.sf.net/sfu/appdyn_sfd2d_oct _______________________________________________ Hsqldb-user mailing list [8]Hsq...@li... [9]https://lists.sourceforge.net/lists/listinfo/hsqldb-user This message has been checked for all known viruses by the Postini Virus Control Centre. ----------------------------------------------------------------------- ------- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: [10]http://p.sf.net/sfu/appdyn_sfd2d_oct _______________________________________________ Hsqldb-user mailing list [11]Hsq...@li... [12]https://lists.sourceforge.net/lists/listinfo/hsqldb-user -------------------------------------------------------------------- ---------- The Windows 8 Center In partnership with Sourceforge Your idea - your app - 30 days. Get started! [13]http://windows8center.sourceforge.net/ what-html-developers-need-to-know-about-coding-windows-8-metro-style -apps/ _______________________________________________ Hsqldb-user mailing list [14]Hsq...@li... [15]https://lists.sourceforge.net/lists/listinfo/hsqldb-user This message has been checked for all known viruses by the Postini Virus Control Centre. ----------------------------------------------------------------------- ------- The Windows 8 Center In partnership with Sourceforge Your idea - your app - 30 days. Get started! [16]http://windows8center.sourceforge.net/ what-html-developers-need-to-know-about-coding-windows-8-metro-style-ap ps/ _______________________________________________ Hsqldb-user mailing list [17]Hsq...@li... [18]https://lists.sourceforge.net/lists/listinfo/hsqldb-user References 1. mailto:fr...@us... 2. mailto:dan...@oc... 3. mailto:fr...@us... 4. http://p.sf.net/sfu/appdyn_sfd2d_oct 5. mailto:Hsq...@li... 6. https://lists.sourceforge.net/lists/listinfo/hsqldb-user 7. http://p.sf.net/sfu/appdyn_sfd2d_oct 8. mailto:Hsq...@li... 9. https://lists.sourceforge.net/lists/listinfo/hsqldb-user 10. http://p.sf.net/sfu/appdyn_sfd2d_oct 11. mailto:Hsq...@li... 12. https://lists.sourceforge.net/lists/listinfo/hsqldb-user 13. http://windows8center.sourceforge.net/ 14. mailto:Hsq...@li... 15. https://lists.sourceforge.net/lists/listinfo/hsqldb-user 16. http://windows8center.sourceforge.net/ 17. mailto:Hsq...@li... 18. https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
From: Daniel S. <dan...@oc...> - 2012-10-26 16:26:08
|
Thanks for the help with that Fred. I spotted a forum post with some other people seeing peculiarities around Oracle syntax and a suggestion was made to try H2 database as an alternative. It integrates into the application in exactly the same way, so was very easy to try - and it does not suffer from this limitation and in some respects supports more Oracle style syntax than HSQLDB. H2 strangely enough didn't have a trunc( date ) function, but adding it took me easily less than 10 minutes and it does support the date arithmetic in the style I am expecting so will allow me to put unit tests around this 1 problematic piece of SQL. I have not yet tried it on a larger application with more nuanced SQL - and I suspect when I do H2 will have other limitations where HSQLDB didn't just as it occurred the other way around - but it has solved my issue for the meantime and I would very much recommend that if people are using in memory databases for testing their code - then I would recommend utilising both h2 and hsqldb as I am able to do in my application due to the extreme similarity in integration - to avoid unnecessarily complicating working code just to fulfil a unit test. Thanks, Daniel Stoner On 26 October 2012 17:16, Fred Toussi <fr...@us...> wrote: > Perhaps this works in Oracle: > > select cast(sysdate - trunc(sysdate) as interval day) from dual > > Although Oracle supports the INTERVAL types, it apparently doesn't > support the sql standard date arithmetic fully, as used in the previous > solution. > > As SYSDATE returns a timestamp with second resolution, HSQLDB returns an > INTERVAL SECOND value for the subtraction. > > In future versions, we may be able to cover these peculiarities better in > the ORA syntax mode. > > Fred > > On Fri, Oct 26, 2012, at 16:38, Daniel Stoner wrote: > > Hi Fred, > > As in the original example: > select (sysdate - trunc(sysdate)) from dual > > In ora_syntax=true mode, this should return 0 (Days) and thus be > consistent with other date arithmetic [IE sysdate+5 is interpreted as > now+5days for instance]. > It is currently returning <LARGE NUMBER> (Seconds). > > If we lived in a perfect world and we matched Oracle behaviour > perfectly, i'd ask for it to return 0.1234 (Fraction of Days) but I suspect > that's beyond what is easily possible. > > Adding the DAY interval to the query is not valid Oracle syntax, and > thus not a possible solution to this. > > I'm not quite sure how a more 'real' example will help, but if you need > one - then I have a table with a date column in it, and I want to know how > many days away from trunc(sysdate) those days are. > > Thanks kindly, > Daniel Stoner > > On 26 October 2012 16:29, Fred Toussi <fr...@us...>wrote: > > Please explain what you want to achieve and provide a more real > example. > > Fred > > On Fri, Oct 26, 2012, at 14:01, Daniel Stoner wrote: > > To be entirely accurate: > > select (sysdate - trunc(sysdate)) from dual > Actually returns the 'fraction of a day' IE seconds/(seconds in day) in > Oracle. IE if I run this right now at 13:58 it returns ~0.583. > > I can live with the answer being returned in whole days (IE 0 in this > case) as I suspect that would be easier, but obviously the perfect > situation would be to have exactly the same result. > > Any pointer towards where in the code I could modify the behaviour would > be fantastic, as I am getting the feeling that doing this in a way that > would keep the SQL syntax compatible with both HSQLDB and Oracle is going > to end up with something horrendous. > > Thanks kindly, > Daniel Stoner > > On 26 October 2012 13:42, Daniel Stoner <dan...@oc...> wrote: > > Hi Fred, > > Thanks very much for the response. > > No mistake, I just wanted to give a simple example of the peculiarity: > > select (sysdate - trunc(sysdate)) DAY from dual > Is not valid syntax in Oracle (I am using 10g but I have also tested on > 11g and it is still not valid). > > Thanks kindly, > Daniel Stoner > > > On 26 October 2012 13:38, Fred Toussi <fr...@us...>wrote: > > For common syntax between HSQLDB and Oracle, do not use the shortcuts > you are using in Oracle. > > SELECT SYSDATE + 5 DAY should be used in the first example. > > SELECT (SYSDATE - TRUNC(SYSDATE) ) DAY -- but this looks like a mistake > as the result is always zero > > SELECT CASE WHEN 1>2 THEN (SYSDATE - TRUNC(SYSDATE) ) HOUR ELSE 999 HOUR > END FROM DUAL > > See the Guide on treatment of DATETIME and INTERVAL types. > > Fred > > On Fri, Oct 26, 2012, at 12:05, Daniel Stoner wrote: > > Hi All, > > I've been using HSQLDB as a test environment with an exclusively > Hibernate application for a long time and it works fantastically. > > I'm now working on an application which uses raw SQL calls only (for > clarity, robustness and reliability reasons this won't be changing, nor am > I being allowed by my managers to change any of the SQL itself), as such > i've encountered a couple of peculiarities around dates and have no idea > how to get around them. > > We use Oracle as our normal database, hence I have set the ora_syntax > variable = true (which I have confirmed, select * from dual works fine). > > I have tried HSQLDB 2.2.8, 2.2.9, 2.0.1 and all have these styles of > peculiarities. > > Peculiarity number 1: > select sysdate + 5 from dual - returns what I as an Oracle user expect - > 5 days + today > > select sysdate - trunc(sysdate) from dual - returns the difference in > seconds. This differs from Oracle, where it would return the result as days. > > Is there a default that is used to translate the date subtraction into > using the DATEDIFF function which is choosing seconds, where perhaps in > ora_syntax mode it should be using DAYS? > > Even if its not a setting, could anyone direct me to perhaps which class > in the hsqldb library I could look at to modify to change the default from > SECONDS to DAYS, as I don't mind having a bit of a modified version of the > hsqldb library sitting in our artifactory. > > > > Peculiarity number 2: > select sysdate - trunc(sysdate) from dual returns a number (Integer - > number of seconds - ignoring peculiarity number 1 - this is just a number) > > select case when 1>2 then sysdate - trunc(sysdate) else 999 from dual > > This returns an error - "Caused by: org.hsqldb.HsqlException: > incompatible data types in combination". > > Now I have read that in reality HSQLDB is returning a 'datetime interval' > object from the sysdate-trunc(sysdate) - and it is only because JDBC cannot > support it that it is translated into a numeric value at the end - but this > is making it really difficult to test this style of function. > > > Since the HSQLDB is being used for tests, and the application will run on > Oracle when it is live, is there any way to have a syntax of SQL supported > by both to complete this style of case statement? > > > Thanks kindly, > Daniel Stoner > > This message has been checked for all known viruses by the Postini Virus Control Centre. > > > ------------------------------------------------------------------------------ > Everyone hates slow websites. So do we. > Make your web apps faster with AppDynamics > Download AppDynamics Lite for free today: > http://p.sf.net/sfu/appdyn_sfd2d_oct > *_______________________________________________* > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > > > > ------------------------------------------------------------------------------ > Everyone hates slow websites. So do we. > Make your web apps faster with AppDynamics > Download AppDynamics Lite for free today: > http://p.sf.net/sfu/appdyn_sfd2d_oct > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > > This message has been checked for all known viruses by the Postini Virus Control Centre. > > > ------------------------------------------------------------------------------ > Everyone hates slow websites. So do we. > Make your web apps faster with AppDynamics > Download AppDynamics Lite for free today: > http://p.sf.net/sfu/appdyn_sfd2d_oct > *_______________________________________________* > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > > > > ------------------------------------------------------------------------------ > The Windows 8 Center > In partnership with Sourceforge > Your idea - your app - 30 days. Get started! > http://windows8center.sourceforge.net/ > what-html-developers-need-to-know-about-coding-windows-8-metro-style-apps/ > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > > This message has been checked for all known viruses by the Postini Virus Control Centre. > > > ------------------------------------------------------------------------------ > The Windows 8 Center > In partnership with Sourceforge > Your idea - your app - 30 days. Get started! > http://windows8center.sourceforge.net/ > what-html-developers-need-to-know-about-coding-windows-8-metro-style-apps/ > *_______________________________________________* > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > > > > ------------------------------------------------------------------------------ > The Windows 8 Center > In partnership with Sourceforge > Your idea - your app - 30 days. Get started! > http://windows8center.sourceforge.net/ > what-html-developers-need-to-know-about-coding-windows-8-metro-style-apps/ > _______________________________________________ > Hsqldb-user mailing list > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > This message has been checked for all known viruses by the Postini Virus Control Centre. |