BigQuery正在转换到其他时区

Question

我将数据存储在谷歌大查询的unixtimestamp中。然而，当用户要求报告时，她将需要按本地时区对数据进行筛选和分组。

数据存储在GMT。用户可能希望看到EST中的数据。报告可能会要求按日期对数据进行分组。

我在这里看不到时区转换功能：

有人知道我如何在bigquery中做到这一点吗？即，在将时间戳转换为不同时区之后，我如何进行分组？

Answer 1

BigQuery中的标准SQL具有内置函数：

DATE(timestamp_expression, timezone)
TIME(timestamp, timezone)
DATETIME(timestamp_expression, timezone)

示例：

SELECT 
original,
DATETIME(original, "America/Los_Angeles") as adjusted
FROM sometable;
+---------------------+---------------------+
| original            | adjusted            |
+---------------------+---------------------+
| 2008-12-25 05:30:00 | 2008-12-24 21:30:00 |
+---------------------+---------------------+

您可以使用标准的IANA时区名称或偏移量。

Answer 2

截至2016年9月，BigQuery已经采用了标准SQL，现在您只需使用"DATE(时间戳，时区)"函数来偏移时区。你可以在这里参考他们的文档：

BigQuery DATE文档

Answer 3

致那些在这里栽跟头的人：

如何将时间戳转换为另一个时区

考虑到TIMESTAMP值一旦构造，就会存储为UTC，并且TIMESTAMP没有构造函数(TIMESTAMP，STRING)，您可以将时间戳转换为另一个时区，方法是先将其转换为DATETIME，然后从新时区中的DATETIME构造新的TIMESTAMP：

SELECT TIMESTAMP(DATETIME(timestamp_field, '{timezone}'))

示例：

SELECT
input_tz,
input,
'America/Montreal' AS output_tz,
TIMESTAMP(DATETIME(input,'America/Montreal')) AS output
FROM (
SELECT 'US/Pacific' AS input_tz, TIMESTAMP(DATETIME(DATE(2021, 1, 1), TIME(16, 0, 0)), 'US/Pacific') AS input
UNION ALL
SELECT 'UTC' AS input_tz, TIMESTAMP(DATETIME(DATE(2021, 1, 1), TIME(16, 0, 0)), 'UTC') AS input
UNION ALL
SELECT 'Europe/Berlin' AS input_tz, TIMESTAMP(DATETIME(DATE(2021, 1, 1), TIME(16, 0, 0)), 'Europe/Berlin') AS input
) t

结果在：

美洲/蒙特利尔2021-01-01 16:00:00 UTC美国/蒙特利尔2021-01-0110:00:00 UTC

行		输入	输出
1	美国/太平洋	2021-01-02 00:00:00 UTC
2	UTC	2021-01-01-01 11:00:00 UTC
3	欧洲/柏林	2021-01-01 15:00:00 UTC	美国/蒙特利尔

您的前提是正确的。如果你这样分组，那么想要EST或EDT的用户将得到错误的日期分组：

GROUP BY UTC_USEC_TO_DAY(ts_field)

但是，只要你计算出用户想要的偏移量，你仍然可以在服务器上进行完整的计算。例如，如果EST比UTC晚5小时，则查询如下：

GROUP BY UTC_USEC_TO_DAY(ts_field - (5*60*60*1000*1000000) )

只需将"5"参数化为以小时为单位的偏移量，就可以了。以下是一个基于样本数据集的样本：

SELECT
COUNT(*) as the_count,
UTC_USEC_TO_DAY(timestamp * 1000000 - (5*60*60*1000*1000000) ) as the_day
FROM
[publicdata:samples.wikipedia]
WHERE
comment CONTAINS 'disaster'
and timestamp >= 1104537600
GROUP BY
the_day
ORDER BY
the_day

您可以删除偏移量，以查看某些编辑如何移动到不同的日期。

要将任何TimeZone DateTime字符串转换为UTC，可以使用BigQuery中支持的TIMESTAMP格式使用PARSE_TIMESTAMP。

例如，要将IST(印度标准时间)字符串转换为UTC，请使用以下命令：

SAFE.PARSE_TIMESTAMP("%a %b %d %T IST %Y", timeStamp_vendor, "Asia/Kolkata")

这里PARSE_TIMESTAMP将IST字符串解析为UTCTIMESTAMP(不是字符串)。添加SAFE作为前缀可以处理错误/null等

要在BigQuery中将其转换为可读字符串格式，请按如下方式使用FORMAT_TIMESTAMP：

FORMAT_TIMESTAMP("%d-%b-%Y %T %Z", SAFE.PARSE_TIMESTAMP("%a %b %d %T IST %Y", timeStamp_vendor, "Asia/Kolkata"))

此示例将采用格式为Fri May 12 09:45:12 IST 2019的IST字符串，并将其转换为12-May-2019 04:15:12 UTC。

将IST替换为所需时区，将Asia/Kolkata替换为相关时区名称，以实现时区的转换

2016更新：看看下面的答案，BigQuery现在提供了时间戳和时区方法。

---

你是对的-BigQuery没有提供任何时间戳转换方法。

在这种情况下，我建议您根据GMT/UTC时间戳字段的维度运行GROUP BY，然后在代码中转换并显示本地时区的结果。

对我来说，TIMESTAMP_SUB和TIMESTAMP_ADD函数完成了任务。当需要将时间戳从UTC转换为PST时，我使用了：

TIMESTAMP_SUB(`timestamp`, INTERVAL 8 HOUR)