[Solved] DATEDIFF issue after HSQLDB 2.2 upgrade

[topherdan]

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

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

[Villeroy]

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

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

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.

[Sliderule]

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

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:

Code: Select all

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.

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.

[Villeroy]

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;

[topherdan]

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?

[Sliderule]

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.

[topherdan]

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!