How can I get the difference in seconds between 2 timestamps, via the ODBC driver?
Using ODBC for a simple query (not that I use cast (... as timestamp) to have a standalone line, the actual query runs against a table with timestamp data):
select unix_timestamp(cast('2019-02-01 01:02:03' as timestamp)) as tto
I got the error message:
unix_timestamp is not a valid scalar function or procedure call
I could not find any configuration option that would change this. Native query is disabled (because I am using prepared statements) and other functions work fine. My guess is that unix_timestamp() (without parameter) is deprecated, and the driver is a bit enthusiastic about preventing using the function.
I tried to work around the problem, and I cast the timestamp as bigint instead of using the unix_timestamp function:
select cast(cast('2019-02-01 01:02:03' as timestamp) as bigint)
This works fine! But when I try to get the diff of 2 timestamps:
select cast(cast('2019-02-01 01:02:03' as timestamp) as bigint) - cast(cast('2019-02-01 01:02:03' as timestamp) as bigint)
I got the message
Operand types SQL_WCHAR and SQL_WCHAR are incompatible for the binary minus operator
(but then only for complex queries, not if the query consists only of this select).
The driver will accept a diff between 2 timestamps, but then I end up with an interval type, which I cannot convert back to seconds.
I would consider that those are bugs in the ODBC drier, but I cannot contact Hortonworks because I am not a paying customer, and I cannot contact Simba either because I am not a paying customer.