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

Обновление 2016 г.: Посмотрите ответы ниже, BigQuery теперь предоставляет методы временной метки и часового пояса.

Вы правы - BigQuery не предоставляет никаких методов преобразования меток времени.

В этом случае я предлагаю вам запустить GROUP BY на основе размеров поля метки времени GMT / UTC, а затем преобразовать и отобразить результат в местном часовом поясе в вашем коде.

Стандартный SQL в BigQuery имеет встроенные функции :

DATE(timestamp_expression, timezone)
TIME(timestamp, timezone)
DATETIME(timestamp_expression, timezone)

SELECT 
   original,
   DATETIME(original, "America/Los_Angeles") as adjusted
FROM sometable;

+---------------------+---------------------+
| original            | adjusted            |
+---------------------+---------------------+
| 2008-12-25 05:30:00 | 2008-12-24 21:30:00 |
+---------------------+---------------------+

Вы можете использовать стандартные имена или смещения часовых поясов IANA .

С сентября 2016 года BigQuery принял стандартный SQL, и теперь вы можете просто использовать функцию «DATE (timestamp, timezone)» для смещения часового пояса. Вы можете ссылаться на их документы здесь:

документы BigQuery DATE

Ваша предпосылка верна. Если вы группируете так, то пользователи, которые хотят EST или EDT, получат неправильную группировку по дате:

GROUP BY UTC_USEC_TO_DAY(ts_field)

Но до тех пор, пока вы выясняете смещение, которое хочет ваш пользователь, вы все равно можете выполнить полный расчет на сервере. Например, если EST отстает от UTC на 5 часов, запросите такой:

GROUP BY UTC_USEC_TO_DAY(ts_field - (5*60*60*1000*1000000) )

Просто настройте «5» на смещение в часах, и все готово. Вот пример, основанный на одном из наборов данных:

SELECT
  COUNT(*) as the_count,
  UTC_USEC_TO_DAY(timestamp * 1000000 - (5*60*60*1000*1000000) ) as the_day
FROM
  [publicdata:samples.wikipedia]
WHERE
  comment CONTAINS 'disaster'
  and timestamp >= 1104537600
GROUP BY
  the_day
ORDER BY
  the_day

Вы можете удалить смещение, чтобы увидеть, как некоторые правки переносятся в разные дни.

Чтобы преобразовать любую строку TimeZone DateTime в UTC, можно использовать PARSE_TIMESTAMP, используя поддерживаемые TIMESTAMP форматы в BigQuery.

Например, чтобы преобразовать строку IST (стандартное индийское время) в UTC, используйте следующее:

SAFE.PARSE_TIMESTAMP("%a %b %d %T IST %Y", timeStamp_vendor, "Asia/Kolkata")

Здесь PARSE_TIMESTAMP анализирует строку IST в формате UTC TIMESTAMP (не строку). Добавление SAFE в качестве префикса устраняет ошибки / нули и т. Д.

Чтобы преобразовать это в читаемый строковый формат в BigQuery, используйте FORMAT_TIMESTAMP следующим образом:

FORMAT_TIMESTAMP("%d-%b-%Y %T %Z", SAFE.PARSE_TIMESTAMP("%a %b %d %T IST %Y", timeStamp_vendor, "Asia/Kolkata"))

В этом примере строка IST формата Fri May 12 09:45:12 IST 2019 преобразуется в 12-May-2019 04:15:12 UTC.

Замените IST на требуемый часовой пояс и Asia/Kolkata на соответствующее имя часового пояса, чтобы добиться преобразования для вашего часового пояса.

Тем, кто здесь спотыкается:

Если вы делаете очистку данных и попадаете в следующую ситуацию:

1. у ваших данных правильное время и дата
2. у ваших данных неправильный часовой пояс
3. вы хотите сохранить время и дату, но изменить часовой пояс на правильный

Затем используйте

SELECT TIMESTAMP(DATETIME(timestamp_field), '{timezone}')

Пример:

SELECT
    CURRENT_TIMESTAMP(),
    TIMESTAMP(DATETIME(CURRENT_TIMESTAMP()), 'America/Montreal'),

приводит к:

2021-07-01 20:39:49.282752 UTC
2021-07-02 00:39:49.282752 UTC

Надеюсь, это может быть полезно! :)

Для меня функции TIMESTAMP_SUB и TIMESTAMP_ADD сделали свою работу. Когда нужно было преобразовать метку времени из UTC в PST, я использовал:

TIMESTAMP_SUB(`timestamp`, INTERVAL 8 HOUR)