Denodo: Converting unix epoch timestamp to datetime

How to convert unix epoch based timestamp to datetime in Denodo? (Denodo does not have the from_unixtime function).

tl;dr; use AddSecond (over GetTimeFromMillis) to convert unix-timestamp (epoch timestamps) to datetime because of performance differences based on where the query is executed.

Denodo (v9) does not have the from_unixtime function, but it does have a similar function GetTimeFromMillis (https://community.denodo.com/docs/html/browse/9.0/en/vdp/vql/functions/datetime_functions/datetime_functions)

Normally if from_unixtime function was available one could call the function like this:

select from_unixtime(1447430881)

which would output: 2015-11-13 16:08:01.000. (from_unixtime takes as input the number of seconds since '1970-01-01 00:00:00' UTC)

In Denodo, one can uses the addsecond method and get the date-time in a similar method as from_unixtime. (addsecond is my recommended function).

select AddSecond(cast('1970-01-01' as timestamp), 1447430881)

outputs: 2015-11-13 16:08:01.000

But one can also use GETTIMEFROMMILLIS, which will output the value in timezone format. If you are looking for a replacement for from_unixtime, then its important to note that the input to GETTIMEFROMMILLIS is in milliseconds and is output in your local timezone format. So if you need it in UTC, you will need to convert it to UTC timezone.

(see important caveats at the end based on which my recommendation is to use AddSecond)

Here is an example converting the unix timestamp in denodo using both addSecond and GetTimeFromMillis

select 1447430881 epoch_time, 
AddSecond(cast('1970-01-01' as timestamp),1447430881) add_second,
GetTimeFromMillis(1447430881000) get_time_from_millis_local_tz, 
GetTimeFromMillis(1447430881000) AT TIME ZONE 'UTC' get_time_from_millis_utc;

IMPORTANT Note about input being big-int:

Unix Epoch Time is defined as the number of seconds since 1970-01-01. This might mean that it would be likely stored as integer in your database. If you multiply that value by 1000, then it will overflow and the calculation will be incorrect. The following sql demonstrates the danger and how to correct it in Denodo:

with unix_epoch as 
(select 1447430881  as timestamp)
select timestamp,
GetTimeFromMillis(timestamp * 1000) get_time_from_millis_local_tz_wrong, 
GetTimeFromMillis(cast(timestamp as INT8) * 1000) get_time_from_millis_local_tz_correct
from unix_epoch;

Extremely IMPORTANT Note (about where processing occurs):

This may vary based on your backend database used by Denodo. In my case, I am using Athena and I found that AddSecond pushes back the query to the backend (I always prefer this to processing occuring on Denodo's servers).
AddSecond based query:

select ADDSECOND(cast('1970-01-01' as timestamp), createddate ) createddatetime
from athena_based_table;

Pushed back query:

SELECT date_add('second', (t0.createddate), cast('1970-01-01'
 as TIMESTAMP)) AS createddatetime FROM 
athena_based_table t0 LIMIT
 150

GetTimeFromMillis based query:

select GETTIMEFROMMILLIS(createddate) createddatetime
from athena_based_table;

Pushed back query:

SELECT t0.createddate AS createddate FROM 
athena_based_table t0

Based on the above, Denodo will be performing the conversion of the value. If you had a filter on createddate, there would be a major performance impact (following query took took 1612 ms).

where ADDSECOND(cast('1970-01-01' as timestamp), createddate ) >= '2024-10-20'

Whereas the following query took 56915 ms (3500% longer)

where GETTIMEFROMMILLIS(createddate) >= '2024-10-20

Links

https://www.epochconverter.com/

https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_from-unixtime

https://community.denodo.com/docs/html/browse/9.0/en/vdp/vql/functions/datetime_functions/datetime_functions#gettimefrommillis