Convert Unixtime to MMddyyyy

Question

I'm trying to convert a column which has unixtime (ex 1542862806000) to regular DTS

select unix_timestamp(column_name) from table;

But i get error:

AnalysisException: No matching function with signature: unix_timestamp(BIGINT).

My column type is bigint

Vamsi Prabhala · Accepted Answer · 2018-11-29T15:27:17.713

1

You are looking for from_unixtime not unix_timestamp.

select from_unixtime(cast(column_name/1000 as bigint),'MMddyyyy') 
from table

unix_timestamp converts a date/date format string to a bigint representing the number of seconds since 1970-01-01 00:00:00 UTC.

from_unixtime takes a bigint input and converts it to the required date format.

edited Nov 29 '18 at 15:27

answered Nov 29 '18 at 15:18

Vamsi Prabhala

1

i forgot the second argument. try the edited version. – Vamsi Prabhala Nov 29 '18 at 15:20
1

Now I get NULL values – TheNewGuy Nov 29 '18 at 15:22
1

you should divide the input by 1000, based on the input shown. – Vamsi Prabhala Nov 29 '18 at 15:27

1 Answers1