FROM UNIX TIME in Presto syntax

user_8431310 0
Viewed 11816

I'm currently trying to collect data that falls between 2 dates via Unix timestamp. All of our dates are stored as VARCHARs to the CAST function is used.

The line in my query reads as:

FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x') between 
CAST(d.start_date AS TIMESTAMP) and CAST(d.end_date AS TIMESTAMP)

This returns as error:

Function unix_timestamp not registered

I also tried:

CAST(from_unixtime(unixtime) AS DATE) between 
CAST(start_date AS DATE) and CAST(end_date AS DATE)

This produces the error:

Column unixtime cannot be resolved

Any suggestions?

presto casting unix sql
2 Answers

Presto does not support unix_timestamp() function. You need to convert your varchar to date.

So:

now() BETWEEN
date_parse(start_date, '%Y-%m-%d %H:%i:%s') AND
date_parse(end_date, '%Y-%m-%d %H:%i:%s')

Adjust the date format string as per scenario.

For a full list of Presto date and time function, refer to: https://prestodb.io/docs/current/functions/datetime.html

user_34615490

I've used the code below to convert a unix timestamp to a date. You should then be able to compare it to the other two dates.

CAST(from_unixtime(unix_ts_col) AS DATE)

In the database I use, the unix timestamp has been stored as a string, so I had to cast it to an integer first.

CAST(from_unixtime(CAST(unix_ts_col AS INTEGER)) AS DATE);
user_36931770
Related