Casting unix time to date in Presto

Question

I have timestamps stored in time since epoch (ms) and I would like to query and display results using a date formatted like 'yyyy-mm-dd'.

score 35 · Accepted Answer · answered Jun 08 '17 at 00:15

35

cast(from_unixtime(unixtime) as date)

answered Jun 08 '17 at 00:15

Christina Foley

cast((from_unixtime(unixtime)) as date) this worked for me. – Siddhant Saxena May 31 '18 at 06:34
the unixtime is in ms as per the question, hence we should be using `cast(from_unixtime(unixtime/1000) as date)` – Sairam Cherupally Apr 12 '23 at 08:09

score 0 · Answer 2 · edited Nov 02 '21 at 11:24

0

as you mentioned epoc(ms) division by 1000 would convert it to seconds.

date(from_unixtime(min(ts)/1000))

This helped me my epoch time is saved as BigInt and stored to be accurate to milliseconds.

edited Nov 02 '21 at 11:24

Suraj Rao

answered Oct 19 '21 at 08:26

kickstart530

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 19 '21 at 08:27
https://stackoverflow.com/editing-help – Yunnosch Oct 19 '21 at 08:39

score 0 · Answer 3 · answered Oct 14 '22 at 18:08

Use following to get the answer in YYYY-MM-DD format

format_datetime(from_unixtime(unixtime), 'Y-MM-dd')

Example:

format_datetime(from_unixtime(1664582792), 'Y-MM-dd')

Output --> 2022-10-01

Using cast as date as follows

cast(from_unixtime(1664582792) as date)

will give output as

2022-10-01 00:00:00.000

3 Answers3