bigquery converting the string datetime with timezone

Question

A table with terabytes of data in bigquery got multiple columns set as string format but actually they contain datetime strings like

2016-10-24 15:00:00

I tried answer from this link to convert (CAST) the fields into timestamp format as below

SELECT
   CAST( MURDER_DATE AS TIMESTAMP) AS CONVERTED_MURDER_DATE, *
FROM `death_list`;

That works but it converts all strings into timestamps with UTC timezone as below

2007-03-23 15:00:00.000 UTC

I need the data in a different timezone. Any clue?

Answer 1

Try using

DATETIME(CAST( MURDER_DATE AS TIMESTAMP), "Australia/Sydney"))

Answer 2

To my view, it seems to be a current limitation of BigQuery:

• Timestamp type is always stored in UTC format. And you have no way to add any "timezone" information to it.
• Datetime type nor stores any information about the timezone. You could still have a internal convention in your team/company that says that all the Datetime columns are stored in your local timezone, but I personally find it very awkward.

What we've decided so far in our company is to store everything in Timestamp (thus UTC format), and we never use Datetime due to lack of precision regarding the time zone. Then, if a client wants to get the information in another timezone, it has to do the conversion itself when reading the data.