how convert int64 to date?-CodePudding

I have a table with an INT64 column called last_modification_time and values are as below:

1640089029711

1640100925628

I would like to see it in a date format. TIMESTAMP_MICROS(last_modification_time) convert above values to

1970-01-19 23:34:49.029711 UTC

1970-01-19 23:35:00.925628 UTC

which definitely is incorrect. Would you please let me know how I can convert to a date format? I am expecting to see a 2021 date as modification dates are mostly 2021 for my data.

Expected result: a 2021 date Thanks

CodePudding user response：

Use below

select last_modification_time, timestamp_millis(last_modification_time)

if applied to sample data in your question - output is

CodePudding user response：

It's an EPOCH.

You can try it here - https://www.epochconverter.com/

Your number "1640089029711" means GMT: Tuesday, December 21, 2021 12:17:09.711 PM

You can find the answer here - How do you convert epoch to date in BigQuery?