[Solved] DATEDIFF issue after HSQLDB 2.2 upgrade

Creating tables and queries

[Solved] DATEDIFF issue after HSQLDB 2.2 upgrade

Postby topherdan » Fri Feb 10, 2012 10:33 am

Hi all,

I had some queries that were using DATEDIFF to calculate the differences between two TIME fields, which were working before I upgraded my database engine to HSQLDB 2.2 from 1.8. Now, I get an "Incompatible Types" problem when I try to run the query. TIMESTAMPDIFF gave me the same error. When I run the following query:

Code: Select all   Expand viewCollapse view
SELECT "A"."TABLE_TYPE", "A"."HSQLDB_TYPE", "A"."TABLE_NAME", "B"."COLUMN_NAME", "B"."TYPE_NAME", "B"."COLUMN_SIZE", "B"."COLUMN_DEF" AS "Default", "B"."NULLABLE"

FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES" AS "A", "INFORMATION_SCHEMA"."SYSTEM_COLUMNS" AS "B"

WHERE "A"."TABLE_SCHEM" = 'PUBLIC' AND "A"."TABLE_NAME" = "B"."TABLE_NAME"

ORDER BY "A"."TABLE_TYPE", "A"."TABLE_NAME", "B"."ORDINAL_POSITION"


I get my column types listed as TIME fields.

Any help would be great!
Chris
Last edited by topherdan on Thu Feb 16, 2012 5:31 am, edited 1 time in total.
topher

OpenOffice 3.3 on Windows XP
topherdan
 
Posts: 22
Joined: Wed Jan 25, 2012 3:19 pm

Re: Datetime Arithmetic not working after HSQLDB 2.2 upgrad

Postby Villeroy » Fri Feb 10, 2012 3:28 pm

HSQLDB 2.2 documentation wrote:TIMESTAMPDIFF

TIMESTAMPDIFF ( <tsi datetime field>, <datetime value expr 1>, <datetime value expr 2>)

<tsi datetime field> ::= SQL_TSI_FRAC_SECOND | SQL_TSI_SECOND | SQL_TSI_MINUTE | SQL_TSI_HOUR | SQL_TSI_DAY | SQL_TSI_WEEK | SQL_TSI_MONTH | SQL_TSI_QUARTER | SQL_TSI_YEAR


The SQL_TSI_ values are constants to be used literally like CURRENT_TIME.

I tested the function with this expression in one of my HSQLDB databases:
Code: Select all   Expand viewCollapse view
SELECT "TS", TIMESTAMPDIFF(SQL_TSI_MINUTE,"TS",CURRENT_TIMESTAMP) AS "X" FROM "VW" ORDER BY "TS" DESC

VW is the name of the table, TS is the stamp field. SQL_TSI_MINUTE and CURRENT_TIMESTAMP are literals.
The query needs to be run in direct SQL mode since Base has no concept of this functionality.

Code: Select all   Expand viewCollapse view
SELECT "TS", DATEDIFF('mi',"TS",CURRENT_TIMESTAMP) AS "X" FROM "VW" ORDER BY TS DESC

should work in parsed SQL but actually it requires direct SQL for some reason I don't know yet.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28358
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Datetime Arithmetic not working after HSQLDB 2.2 upgrad

Postby Sliderule » Sat Feb 11, 2012 12:52 am

Chris:

In your post above, the SQL you provided, did NOT include any DATEDIFF function, therefore, it is impossible to tell you exactly what is wrong.

BUT, I suspect, or put another way, I can guess, that a solution is easy. Let me explain. The two fields you want to perform the DATEDIFF on, must be TIMESTAMP fields . . . not just TEXT field that look like TIMESTAMP fields, but, actual TIMESTAMP field type.

So, please use the following example . . . that can be run EITHER with or withOUT the OpenOffice Base parser. To try it, just copy and paste the SQL below, and run it with OpenOffice Base:

Code: Select all   Expand viewCollapse view
Select
   CURRENT_TIMESTAMP as "Current_TimeStamp",
   DATEDIFF('hour', CURRENT_TIMESTAMP, CAST('2012-02-10 13:05:00' as TIMESTAMP) ) as "DateDiff hour",
   DATEDIFF('hh', CURRENT_TIMESTAMP, CAST('2012-02-10 13:05:00' as TIMESTAMP) ) as "DateDiff hh",
   DATEDIFF('minute', CURRENT_TIMESTAMP, CAST('2012-02-10 13:05:00' as TIMESTAMP) ) as "DateDiff minute",
   DATEDIFF('mi', CURRENT_TIMESTAMP, CAST('2012-02-10 13:05:00' as TIMESTAMP) ) as "DateDiff mi"
From INFORMATION_SCHEMA.SYSTEM_TABLES
Where TABLE_TYPE = 'TABLE'


Or, if you have two fields defined as only TIME, use the following SQL, and, change "MyTimeField1" and "MyTimeField2" to the correct names in your table: :super:

Code: Select all   Expand viewCollapse view
Select
   CURRENT_DATE as "Current_Date",
   DATEDIFF('hh', CAST(CURRENT_DATE || ' ' || "MyTimeField1" as TIMESTAMP), CAST(CURRENT_DATE || ' ' || "MyTimeField2" as TIMESTAMP)) as "DateDiff hh",
   DATEDIFF('hour', CAST(CURRENT_DATE || ' ' || "MyTimeField1" as TIMESTAMP), CAST(CURRENT_DATE || ' ' || "MyTimeField2" as TIMESTAMP)) as "DateDiff hour",
   DATEDIFF('mi',  CAST(CURRENT_DATE || ' ' || "MyTimeField1" as TIMESTAMP), CAST(CURRENT_DATE || ' ' || "MyTimeField2" as TIMESTAMP))) as "DateDiff mi",
   DATEDIFF('minute',  CAST(CURRENT_DATE || ' ' || "MyTimeField1" as TIMESTAMP), CAST(CURRENT_DATE || ' ' || "MyTimeField2" as TIMESTAMP))) as "DateDiff minute"
From INFORMATION_SCHEMA.SYSTEM_TABLES
Where TABLE_NAME = 'TABLES'


Eplanation: In the above, using the DATEDIFF function, using different first parameters for demonstration, I am using the CAST function . . . CAST('2012-02-10 13:05:00' as TIMESTAMP) . . . to change a text string into a TIMESTAMP. Easy, when you know how. :bravo:

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1239
Joined: Thu Nov 29, 2007 9:46 am

Re: Datetime Arithmetic not working after HSQLDB 2.2 upgrad

Postby Villeroy » Sat Feb 11, 2012 1:36 am

Sliderule wrote:So, please use the following example . . . that can be run EITHER with or withOUT the OpenOffice Base parser.

And the one and only reason why my above query with DATEDIFF failed was:

ORDER BY TS DESC;
should be
ORDER BY "TS" DESC;

:roll:
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28358
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Datetime Arithmetic not working after HSQLDB 2.2 upgrad

Postby topherdan » Thu Feb 16, 2012 4:46 am

Thanks for the responses! I was able to get some functionality out of it by using the CAST function, but I was only casting the time value as a timestamp by itself, without a date included. It was giving me correct answers, but I'm worried about it possibly calculating incorrect values in the future. Is it absolutely necessary that I include dates when casting as a timestamp?
topher

OpenOffice 3.3 on Windows XP
topherdan
 
Posts: 22
Joined: Wed Jan 25, 2012 3:19 pm

Re: Datetime Arithmetic not working after HSQLDB 2.2 upgrad

Postby Sliderule » Thu Feb 16, 2012 5:04 am

topherdan wrote:I was able to get some functionality out of it by using the CAST function, but I was only casting the time value as a timestamp by itself, without a date included. It was giving me correct answers, but I'm worried about it possibly calculating incorrect values in the future. Is it absolutely necessary that I include dates when casting as a timestamp?

That depends, if your times span a date .. . for example, crossing midnight.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1239
Joined: Thu Nov 29, 2007 9:46 am

Re: Datetime Arithmetic not working after HSQLDB 2.2 upgrad

Postby topherdan » Thu Feb 16, 2012 5:30 am

They should never span midnight, but I suppose for the sake of error reduction I'll add in the date to the CAST statement.

Thanks again for all your help!
topher

OpenOffice 3.3 on Windows XP
topherdan
 
Posts: 22
Joined: Wed Jan 25, 2012 3:19 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 0 guests