Я храню данные в unixtimestamp по большому запросу google. Однако, когда пользователь будет запрашивать отчет, ей потребуется фильтрация и группировка данных по ее местному часовому поясу.
Данные хранятся в GMT. Пользователь может пожелать увидеть данные в EST. В отчете может быть указано, что данные должны быть сгруппированы по дате.
Я не вижу функцию преобразования часового пояса здесь:
Кто-нибудь знает, как я могу это сделать в bigquery? т.е. как мне группировать после преобразования временной метки в другой часовой пояс?
Обновление 2016 года: посмотрите ответы ниже, теперь BigQuery предоставляет методы меток времени и часовых поясов.
Вы правы - BigQuery не предоставляет методов преобразования меток времени.
В этом случае я предлагаю вам запустить GROUP BY на основе измерений поля метки времени GMT/UTC, а затем преобразовать и отобразить результат в местном часовом поясе в своем коде.
По состоянию на сентябрь 2016 года BigQuery принял стандартный SQL, и теперь вы можете просто использовать функцию DATE (timestamp, timezone) для смещения по часовой стрелке. Вы можете ссылаться на свои документы здесь:
Стандартный 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 или смещения.
Ваше предположение верно. Если вы группируете это, то пользователи, которые хотят EST или EDT, получат неправильную группировку даты:
GROUP BY UTC_USEC_TO_DAY(ts_field)
Но пока вы выясняете смещение, которое хочет ваш пользователь, вы все равно можете выполнить полный расчет на сервере. Например, если EST на 5 часов меньше UTC, тогда выполните следующие запросы:
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 - соответствующим именем часового пояса, чтобы выполнить преобразование для вашего часового пояса.