bigquery使用timezone转换字符串日期时间

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

bigquery中包含TB级数据的表将多个列设置为字符串格式，但实际上它们包含日期时间字符串

2016-10-24 15:00:00

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

我尝试从此链接回答将字段转换（CAST）为时间戳格式，如下所示

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

这有效，但它将所有字符串转换为UTC时区的时间戳，如下所示

2007-03-23 15:00:00.000 UTC

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

我需要不同时区的数据。任何线索？

2 个解决方案

#1

Try using

尝试使用

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

#2

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

在我看来，它似乎是BigQuery的当前限制：

Timestamp type is always stored in UTC format. And you have no way to add any "timezone" information to it.
时间戳类型始终以UTC格式存储。而且您无法向其添加任何“时区”信息。
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.
日期时间类型也不存储有关时区的任何信息。您的团队/公司中仍然可以有一个内部约定，即所有Datetime列都存储在您当地的时区，但我个人认为它很尴尬。

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.

到目前为止，我们公司决定将所有内容存储在Timestamp中（因此为UTC格式），由于时区精度不高，我们从不使用Datetime。然后，如果客户想要在另一个时区获取信息，则必须在读取数据时进行转换。

#1