BigQuery timestamp_trunc不应用时区
BigQuery timestamp_trunc不应用时区
提问于 2022-02-16 17:27:57
根据Google BigQuery文档，这是如何将时区应用于timestamp_trunc：
SELECT
timestamp_value AS timestamp_value,
TIMESTAMP_TRUNC(timestamp_value, WEEK(MONDAY), "UTC") AS utc_truncated,
TIMESTAMP_TRUNC(timestamp_value, WEEK(MONDAY), "Pacific/Auckland") AS nzdt_truncated
FROM (SELECT TIMESTAMP("2017-11-06 00:00:00+12") AS timestamp_value);
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+-------------------------+-------------------------+
| timestamp_value | utc_truncated | nzdt_truncated |
+-------------------------+-------------------------+-------------------------+
| 2017-11-05 12:00:00 UTC | 2017-10-30 00:00:00 UTC | 2017-11-05 11:00:00 UTC |
+-------------------------+-------------------------+-------------------------+我运行了这个(我的时间戳列是‘时间戳’类型，我的数据在UTC-0中)：
select TIMESTAMP_TRUNC(timestamp, MINUTE) as timestamp
,TIMESTAMP_TRUNC(timestamp, MINUTE, "America/New_York") as ny_timestamp
from table并在两列中得到相同的值。
文档声称在分钟截断时支持时区，但它似乎没有将我的时间戳转换为正确的区域。
如果你知道我做错了什么，请告诉我。我很困惑。任何帮助都是非常感谢的。
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-02-17 06:02:57
这似乎是TRUNC意想不到的行为。根据测试，我所做的时区转换在HOUR和MINUTE上不起作用。我为此创建了一个公共问题跟踪器。
同时，您可以在截断之前转换时区。见以下查询：
WITH CTE as (
SELECT CURRENT_TIMESTAMP() as test_time
)
SELECT
TIMESTAMP_TRUNC(test_time, MINUTE) as utc_timestamp
,TIMESTAMP_TRUNC(TIMESTAMP(DATETIME(test_time, "America/New_York")), MINUTE) as ny_timestamp
FROM CTE输出：
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71146549
复制相关文章
相似问题
腾讯云开发者
