如何根据bigquery中的timezone列将UTC时间转换为本地时区? [英] How to convert UTC time to local timezones based on timezone column in bigquery?
本文介绍了如何根据bigquery中的timezone列将UTC时间转换为本地时区?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我一直在尝试使用GBQ中的标准SQL将每个UTC时间转换回适当的本地时区,但是找不到动态地执行此操作的好方法,因为我在数据库中可能有很多不同的时区名称.我想知道是否有人有主意?
I've been trying to convert each UTC time back to the appropriate local timezone using standard SQL in GBQ, but couldn't find a good way to do it dynamically because I might have tons of different timezone name within the database. I'm wondering if anyone has an idea?
我的表格包含2个不同的列(请参见屏幕截图)
The table I have contains 2 different columns (see screenshot)
推荐答案
以下示例适用于BigQuery标准SQL
Below example is for BigQuery Standard SQL
#standardSQL
WITH `project.dataset.yourtable` AS (
SELECT 'Pacific/Honolulu' timezone, TIMESTAMP '2020-03-01 03:41:27 UTC' UTC_timestamp UNION ALL
SELECT 'America/Los_Angeles', '2020-03-01 03:41:27 UTC'
)
SELECT *,
DATETIME(UTC_timestamp, timezone) AS local_time
FROM `project.dataset.yourtable`
有输出
Row timezone UTC_timestamp local_time
1 Pacific/Honolulu 2020-03-01 03:41:27 UTC 2020-02-29T17:41:27
2 America/Los_Angeles 2020-03-01 03:41:27 UTC 2020-02-29T19:41:27
这篇关于如何根据bigquery中的timezone列将UTC时间转换为本地时区?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文