CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
dijeah
dijeah

Reputation: 303

How to convert epoch to datetime in bigquery?

I have a column in a bigquery table with epoch values in milliseconds. These include negative epoch values for dates before 1970 also.

How do I convert them into DATETIME format using Standard and Legacy SQL to the format:1998-10-18 13:45:55?

This should work even for dates before 1970 .i.e. negative epoch values.

I tried EXTRACT(DATETIME FROM TIMESTAMP_MILLIS(-2494865480000));

But it returns me a value with a T included in it: 1890-12-10T05:48:40

Upvotes: 0

Views: 2759

Answers (1)

Tamir Klein
Tamir Klein

Reputation: 3642

Putting aside that I don't understand your expectation on dates before 1970 you can use FORMAT_DATETIME function to format your date result as follow:

SELECT FORMAT_DATETIME("%F %T", EXTRACT(DATETIME FROM TIMESTAMP_MILLIS(-2494865480000)))

The result of this SQL is

1890-12-10 05:48:40

Upvotes: 5

Related Questions