在BigQuery中将时间戳转换为不同的时区

Question

我已经多次仔细阅读了谷歌的文档，但似乎找不到在SELECT查询中将UTC时间戳转换为其他时区（例如Pacific）的简单函数。对于大多数国际时区，我可以使用TIMESTAMP_SUB或TIMESTAMP_ADD减去/添加偏移小时数，但美国夏令时的使用会使事情变得复杂（不必要！）。

请问，我有漏看文档中的内容吗？还是有其他简单的方法可以转换到另一个时区？

- dnaeye

Answer 1

TIMESTAMP类型与UTC相关联。当您将TIMESTAMP转换为其他未绑定特定时区的类型，如STRING、DATE或DATETIME时，可以指定转换的时区，例如：

SELECT EXTRACT(DATE FROM CURRENT_TIMESTAMP()
               AT TIME ZONE 'America/Los_Angeles') AS current_pst_day;

如果你想知道不同时区之间的（当前）小时数，可以使用CURRENT_DATETIME()函数并选择不同的时区来计算时间差：

SELECT
  time_zone,
  DATETIME_DIFF(CURRENT_DATETIME(time_zone),
                CURRENT_DATETIME(), HOUR) AS hours_from_utc
FROM UNNEST(['America/Los_Angeles', 'America/New_York']) AS time_zone;
+---------------------+----------------+
| time_zone           | hours_from_utc |
+---------------------+----------------+
| America/Los_Angeles | -8             |
| America/New_York    | -5             |
+---------------------+----------------+

为了使偏移更加方便，您可以将其封装成一个SQL函数，然后调用该函数将偏移添加到特定的时间戳中：

CREATE TEMP FUNCTION OffsetForTimeZone(t TIMESTAMP, time_zone STRING) AS (
  TIMESTAMP_ADD(t, INTERVAL DATETIME_DIFF(CURRENT_DATETIME(time_zone),
                                          CURRENT_DATETIME(), HOUR) HOUR)
);

SELECT OffsetForTimeZone(CURRENT_TIMESTAMP(), 'America/Los_Angeles');

请记住，这个结果仍然是与UTC相关的TIMESTAMP，尽管它偏移了当前与太平洋时间之间的差异。

- Elliott Brossard

---

我是一个SQL/BigQuery新手，所以我不知道我甚至可以在查询中编写自己的函数！我想这就是唯一的选择，除非Google将其作为内置函数添加。 - dnaeye

只是补充一下，还有format_timestamp函数可以返回一个字符串并处理时区 - https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#format_timestamp - Graham Polley

Answer 2

作为对Elliott答案的补充，我想测试一下时区数学是否能够处理夏令时变化：

#standardSQL
WITH dates AS (
  SELECT TIMESTAMP('2015-07-01') x, 'summer' season
  UNION ALL SELECT TIMESTAMP('2015-01-01') x, 'winter' season
)

SELECT
  season,
  time_zone,
  DATETIME_DIFF(DATETIME(x, time_zone),
                DATETIME(x), HOUR) AS hours_from_utc
FROM UNNEST(['America/Los_Angeles', 'America/New_York']) AS time_zone
CROSS JOIN dates 
ORDER BY 1,2

它的功能包括：

+--------+---------------------+----------------+
| season |      time_zone      | hours_from_utc |
+--------+---------------------+----------------+
| summer | America/Los_Angeles |             -7 |
| summer | America/New_York    |             -4 |
| winter | America/Los_Angeles |             -8 |
| winter | America/New_York    |             -5 |
+--------+---------------------+----------------+

它甚至知道智利在2015年没有经历夏令时：

#standardSQL
WITH dates AS (
  SELECT TIMESTAMP('2014-07-01') x
  UNION ALL SELECT TIMESTAMP('2015-07-01') x
  UNION ALL SELECT TIMESTAMP('2016-07-01') x
)

SELECT
  EXTRACT(YEAR FROM x),
  time_zone,
  DATETIME_DIFF(DATETIME(x, time_zone),
                DATETIME(x), HOUR) AS hours_from_utc
FROM UNNEST(['Chile/Continental', 'America/New_York']) AS time_zone
CROSS JOIN dates 
ORDER BY 2,2


+------+-------------------+----------------+
| f0_  |     time_zone     | hours_from_utc |
+------+-------------------+----------------+
| 2014 | America/New_York  |             -4 |
| 2015 | America/New_York  |             -4 |
| 2016 | America/New_York  |             -4 |
| 2014 | Chile/Continental |             -4 |
| 2015 | Chile/Continental |             -3 |
| 2016 | Chile/Continental |             -4 |
+------+-------------------+----------------+

- Felipe Hoffa

---

尝试使用'America/Phoenix'和'America/Los_Angeles'时，我期望看到一个小时的差异，但两者都显示为“-7”。 - Mikhail Berlyant

谷歌说是-7呢 :) http://i.imgur.com/VH8hkyX.png （洛杉矶上面是-8..你怎么得到的-7？） - Felipe Hoffa

但是现在菲尼克斯时间是7:04，而洛杉矶时间才6:04。这就是我想指出的。 - Mikhail Berlyant

明白了。让我检查一下为什么我突然得到了两个-7。顺便说一下，我刚才在上面的答案中使用了你的代码。还是谢谢你的快速检查和回复 :o) - Mikhail Berlyant

哦，我明白了 - 我使用了你的第二段代码，它只有夏令时，所以这就是为什么两个都返回了-7，当我将其更改为冬令时 - 我得到了我期望的结果。 - Mikhail Berlyant

@FelipeHoffa - “它甚至知道智利在2015年没有经历夏令时”，这真是令人印象深刻！ - Graham Polley

Answer 3

自从我发布了我的问题以来，我一直在使用TIMESTAMP_SUB函数和偏移量来将默认的UTC时间戳转换为我想要的时区；我没有使用Elliot上面的优雅解决方案，因为它要求我创建一个函数，而Tableau的自定义SQL功能不允许这样做。

对于任何感兴趣的人，我现在找到了一种替代方案，尽管很丑陋。BigQuery确实允许在从一种格式转换到另一种格式时使用内置的时区偏移量（例如，“America/Los_Angeles”）。例如，从时间戳到日期时间： SELECT DATETIME(CURRENT_TIMESTAMP(),"America/Los_Angeles") 该示例从当前UTC时间转换为太平洋时间。但是，它现在处于日期时间格式中，Tableau无法识别为日期/时间对象。为了将其重新转换为时间戳格式，我将上述函数封装到TIMESTAMP函数中： SELECT TIMESTAMP(DATETIME(CURRENT_TIMESTAMP(),"America/Los_Angeles")) 愚蠢、不优雅，而且大约慢20%，但它似乎能够工作。

- dnaeye

Answer 4

这应该可以工作：

SELECT CURRENT_DATE("America/Los_Angeles")

- monty jango