Multi tool use

FROM UNIX TIME in Presto syntax

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

unixtime

Any suggestions?

1 Answer
1

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);


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.