CODEWITHSUNDEEP
sqlgoogle-bigquerytimezone
bugsyb
bugsyb

Reputation: 6041

Converting timestamps in google big query (SQL)

I've returned a timestamp in google big query that looks like this: 2019-08-24 19:46:41 UTC

From looking at the raw data, I know that the actual time is 2019-08-24 19:46:31 EST, however, I think it was automatically converted to UTC since I used the timestamp_seconds function. I was wondering if there was a way to convert this to EST without subtracting 5 hours, since I know that in reality the time is correct, just the timezone is wrong.

Thanks!

Upvotes: 0

Views: 341

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Just convert to a datetime:

select datetime(timestamp_expression)

You can now interpret this in whatever timezone you like.

I would strongly, strongly discourage you from putting anything other than UTC in a timestamp data type (a date/time value with a timezone offset is fine). It represents a point-in-time, which is best represented consistently as UTC.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

Standard SQL in BigQuery 

DATETIME(timestamp_expression, 'Europe/Berlin')

Upvotes: 1

Related Questions