Преобразование метки времени в другой часовой пояс в BigQuery

Я несколько раз просматривал документацию Google , но я не могу найти простую функцию (в пределах запроса SELECT) для преобразования метки времени UTC в другой часовой пояс, который в моем случае является Тихоокеанским. Для большинства международных часовых поясов я могу просто использовать TIMESTAMP_SUB или TIMESTAMP_ADD для вычитания / добавления смещенных часов, но использование Соединенными Штатами летнего времени усложняет ситуацию (без необходимости!).



Я что-то пропустил в документации? Или есть какой-то другой способ легко перейти в другой часовой пояс?

658   3  

3 ответов:

Тип 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, хотя и компенсируется текущей разницей между этим и Тихоокеанским временем.
2  
2017-01-10 19:28:21

В качестве дополнения к ответу Эллиотта я хотел проверить, работает ли математика часового пояса вокруг изменений летнего времени: 

#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 |
+--------+---------------------+----------------+

Известно даже, что Чили не прошла DST в 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 |
+------+-------------------+----------------+
1  
2017-01-11 03:35:51

С момента первоначальной публикации моего вопроса я просто использовал функцию TIMESTAMP_SUB со смещением, чтобы преобразовать временную метку UTC по умолчанию в желаемый часовой пояс; я не использовал элегантное решение Эллиота выше, потому что оно требовало от меня создать функцию, которую не позволяет пользовательская функция SQL Tableau.

Для всех, кто заинтересован, я нашел альтернативное решение, хотя и уродливое. BigQuery допускает встроенные смещения часовых поясов (например, "America/Los_Angeles") при преобразовании из одного формат другой. Например, переход от типа timestamp в datetime:

SELECT DATETIME(CURRENT_TIMESTAMP(),"America/Los_Angeles")

Пример идет от текущего времени UTC до тихоокеанского времени. Однако теперь он находится в формате datetime, который Tableau не распознает как объект даты/времени. Чтобы вернуть его в формат временной метки, я вложил функцию выше в функцию TIMESTAMP:

SELECT TIMESTAMP(DATETIME(CURRENT_TIMESTAMP(),"America/Los_Angeles"))

Глупо, неэлегантно и примерно на 20% медленнее, но, кажется, это работает.
0  
2017-06-14 17:47:50

Comments

    Ничего не найдено.
Click here to cancel reply.