Support Questions
Find answers, ask questions, and share your expertise

Hive ODBC cannot use unix_timestamp

Expert Contributor

Short version:

How can I get the difference in seconds between 2 timestamps, via the ODBC driver?

Long version:

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.

Any idea how I could get around this?