如何根据bigquery中的时区列将UTC时间转换为本地时区? [英] How to convert UTC time to local timezones based on timezone column in bigquery?

问题描述

我一直在尝试使用 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 Standard 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中的时区列将UTC时间转换为本地时区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！