Работа с Google BigQuery Date и Timestamp

В BigQuery date и datetime могут быть одного из следующих типов данных:

DATE: календарная дата (например, 2020-01-01)
DATETIME: календарная дата и время (например, 2020-01-01 13:04:11)
TIMEZONE: определенный момент времени, может включать часовой пояс, но по умолчанию используется UTC (например, 2020-01-01 13:04:11-5:00).
TIME: время, отображаемое на часах (например, 13:04:11).

Если вы хотите работать с часовыми поясами, вам нужно использовать TIMESTAMP или DATETIME он является также гибким.


Содержание статьи и быстрые ссылки (навигация по статье)

- Как найти текущий DATETIME
- Изменение формата DATETIME
- Преобразование данных при помощи функции CAST
- Парсинг даты из строки при помощи функции PARSE
- Преобразование из UNIX
- Добавление и вычитание времени
- Группировка даты / Извлечение секунды, минуты, дня, недели, месяца, годы из даты
- Разница во времени между двумя DATE или DATETIME
- Генерация библиотеки дат для использования в качестве справочника в BI
- Навигация по таблицам в партиционированных таблицах
Как найти текущий datetime
Чтобы получить текущую дату или время, вы можете использовать функцию CURRENT в BigQuery.
Синтаксис:
  • CURRENT_DATE ()
  • CURRENT_DATETIME ()
  • CURRENT_TIMESTAMP ()
  • CURRENT_TIME ()
В приведенном ниже примере показано, как использовать функцию CURRENT_DATETIME для получения сегодняшней даты и текущего времени.
SELECT CURRENT_DATETIME() AS now
Примечание. Этот запрос выведет настройки даты и времени вашего компьютера (включая часовой пояс).
Изменение формата datetime
При работе с датой и временем, вы можете изменить формат, в котором они отображаются, с помощью функции FORMAT. Функция FORMAT форматирует дату и время в соответствии с указанным format_string.

Синтаксис:
DATE: FORMAT_DATE (format_string, date)
DATETIME: FORMAT_DATETIME (format_string, datetime)
TIMESTAMP: FORMAT_TIMESTAMP (format_string, timestamp [, timezone])
TIME: FORMAT_TIME (format_string, time)

Ниже приведен список строк форматов, поддерживаемых в BigQuery.
Полный список можно посмотреть по ссылке.
Если я хочу показать свои даты в более распространенном формате, то:
SELECT
  FORMAT_DATETIME ('%Y/%d/%m', CAST ('2022-05-25 13:33:03' AS DATETIME)) AS day,
  FORMAT_DATETIME ('%A', CAST ('2022-05-25 13:33:03' AS DATETIME)) AS weekday,
  FORMAT_DATETIME ('%r', CAST ('2022-05-25 13:33:03' AS DATETIME)) AS time
⚠️ Стоит отметить, что функции FORMAT возвращает STRING, поэтому, если вы хотите использовать результат FORMAT в качестве DATE, это не сработает.

Как преобразовывать типы данных даты

При импорте данных важно помнить о различиях в типах данных. Для успешной синхронизации данных, тип данных, которые вы хотите импортировать, должен быть совместим с типом данных в parser date BigQuery. Дата должна быть в формате:

DATE: ГГГГ-ММ-ДД
DATETIME: ГГГГ-ММ-ДД ЧЧ: ММ:СС
TIMESTAMP: ГГГГ-ММ-ДД ЧЧ: ММ: СС [часовой пояс]
TIME: ЧЧ: ММ: СС
С помощью функции CAST мы можем преобразовать один тип данных в другой.
Синтаксис функции CAST:
CAST (expression AS datatype)
Ниже приведены несколько примеров использования функции CAST.
SELECT
  CAST ('2022-05-25 11:33:04' AS DATETIME) AS datetime,
  CAST ('2022-05-25 11:33:04 Europe/Berlin' AS TIMESTAMP) AS timestamp,
  CAST ('2022-05-25' AS DATE) AS date,
  CAST ('11:33:04' as TIME) time
Оператор PARSE
Чтобы использовать одну из функций PARSE, ваша строка может быть отформатирована, как угодно, вы просто скажете функции, как она должна ее читать. Для каждого типа данных даты/времени существует функция PARSE:

DATE: PARSE_DATE (format_string, date_string)
DATETIME: PARSE_DATETIME (format_string, datetime_string)
TIMESTAMP: PARSE_TIMESTAMP (format_string, timestamp_string[, timezone])
TIME: PARSE_TIME (format_string, time_string)

1. Например, у нас есть таблица с датами, но они указаны в целочисленном виде «20220525»
Преобразуем с строку
CAST (20220525 AS String)
Превращаем в нужный нам формат с помощью оператора PARSE
SELECT PARSE_DATE ('%Y%m%d', Cast (20220525 AS String)) AS datetime
2. Или сложнее пример, у нас есть дата формата – среда, 25 мая 2022 года в 13:40:10
Запрос
SELECT PARSE_DATETIME ('%A %B %d, %Y %H:%M:%S','Wednesday May 25, 2022 13:40:10') AS parsed_datetime
Возможные параметры
%Y — Год с веком в виде десятичного числа — Пример: 2019
%y — год без века в виде десятичного числа — можно смешивать с %C. Если %C не указан, годы 00–68 — это 2000-е, а годы 69–99 — 1900-е
%G — Год согласно ISO 8601 с веком в качестве десятичного числа — Пример: 2021
%m — Месяц в виде десятичного числа — От 01 до 12
%B — Полное название месяца — Пример: январь
%d — день месяца в виде десятичного числа — от 01 до 31
Полный список можно найти по ссылке.

Преобразование UNIX
Timestamp to date BigQuery предлагает несколько вспомогательных функций для получения дат, представленных в виде чисел
DATE:
- DATE_FROM_UNIX_DATE (days since 1970-01-01 00:00:00 UTC)
TIMESTAMP:
- TIMESTAMP_SECONDS (seconds since 1970-01-01 00:00:00 UTC)
- TIMESTAMP_MILLIS (milliseconds since 1970-01-01 00:00:00 UTC)
- TIMESTAMP_MICROS (microseconds since 1970-01-01 00:00:00 UTC)
Например:
SELECT TIMESTAMP_SECONDS (1574356800)
Как добавить или вычесть дату

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

Добавление даты/времени в BigQuery
Чтобы добавить дату и время, используйте следующие операторы:
  • DATE_ADD (date_expression, INTERVAL int64_expression date_part)
  • DATETIME_ADD (datetime_expression, INTERVAL int64_expression date_part)
  • TIMESTAMP_ADD (timestamp_expression, INTERVAL int64_expression date_part)
  • TIME_ADD (time_expression, INTERVAL int64_expression date_part)
Мы используем interval, чтобы сообщить BigQuery, как изменять дату/время. int64_expression - содержит значения в диапазоне от (-9 223 372 036 854 775 808 до +9 223 372 036 854 775 807). Эти значения являются единицами date_part, которые добавляются к функции, например:
SELECT DATETIME_ADD ('2022-04-25', INTERVAL 6 MONTH);
Если вы добавите «-», то эта функция будет вычитать дни.
Пример 2:
SELECT
  CAST('2022-05-25 12:00:00' AS DATETIME) AS original_date,
  DATETIME_ADD(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 HOUR) AS one_hour_later,
  DATETIME_ADD(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 WEEK) AS one_week_later,
  DATETIME_ADD(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 QUARTER) AS one_quarter_later
Допустим у нас есть дата начала отправки груза, продолжительность доставки груза и мы хотим узнать дату прибытия груза
Запрос:
SELECT
  *,
  DATE_ADD(CAST(product_send_start AS DATE), INTERVAL duration_month MONTH) AS product_coming
FROM
  (
    SELECT
      '2022-05-20' AS product_send_start,
      5 AS duration_month
    UNION ALL
(    SELECT
      '2021-12-18' AS product_send_start,
      12 AS duration_month)
    UNION ALL
(    SELECT
      '2022-06-12' AS product_send_start,
      25 AS duration_month)
  ) AS table_1
Поддерживаются следующие значения date_part:
ДЕНЬ - DAY
ЧАС - HOUR
МИНУТА - MINUTE
СЕКУНДА - SECOND
МИЛЛИСЕКУНДЫ - MILLISECOND
МИКРОСЕКУНДЫ - MICROSECOND
НЕДЕЛЯ - WEEK
ЧЕТВЕРТЬ - QUARTER
МЕСЯЦ - MONTH
ГОД - YEAR

Вычитание даты в BigQuery
Чтобы вычесть из даты/времени, нужно использовать следующие операторы:
  • DATE_SUB (date_expression, INTERVAL int64_expression part)
  • DATETIME_SUB (datetime_expression, INTERVAL int64_expression part)
  • TIMESTAMP_SUB (timestamp_expression, INTERVAL int64_expression part)
  • TIME_SUB (time_expression, INTERVAL int64_expression part)
Например:
SELECT DATETIME_SUB (DATETIME "2022-05-20 16:42:05", INTERVAL 1 DAY) AS minus_day
SELECT
  CAST('2022-05-25 12:00:00' AS DATETIME) AS original_date,
  DATETIME_SUB(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 HOUR) AS one_hour_earlier,
  DATETIME_SUB(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 WEEK) AS one_week_earlier,
  DATETIME_SUB(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 QUARTER) AS one_quarter_earlier
Усложним задачу, нам нужно вычесть 2 дня. Для этого мы будем использовать разные интервалы DATE_SUB в зависимости от дня недели.
Запрос:
SELECT
  due_arrival,
  FORMAT_DATE('%A', due_arrival) AS weekday,
  CASE WHEN EXTRACT(DAYOFWEEK FROM due_arrival) IN (2, 3) 
  THEN DATE_SUB(due_arrival, INTERVAL 5 DAY) 
  WHEN (EXTRACT(DAYOFWEEK FROM due_arrival) = 1) 
  THEN DATE_SUB(due_arrival, INTERVAL 4 DAY) 
  ELSE DATE_SUB(due_arrival, INTERVAL 3 DAY) END AS two_days_ago,
  FORMAT_DATE('%A', CASE WHEN EXTRACT(DAYOFWEEK FROM due_arrival) IN (2, 3) 
  THEN DATE_SUB(due_arrival, INTERVAL 5 DAY) 
  WHEN (EXTRACT(DAYOFWEEK FROM due_arrival) = 1) 
  THEN DATE_SUB(due_arrival, INTERVAL 4 DAY) 
  ELSE DATE_SUB(due_arrival, INTERVAL 3 DAY) END) AS weekday_2d_ago
FROM
  (
    SELECT
      CAST('2022-05-25' AS DATE) AS due_arrival
    UNION ALL
(    SELECT
      CAST('2021-09-20' AS DATE) AS due_arrival)
    UNION ALL
(    SELECT
      CAST('2022-12-15' AS DATE) AS due_arrival)
  ) AS table_1
Пример: парсинга UNIX метки из куки гугла
SELECT 
TIMESTAMP_SUB(TIMESTAMP_SECONDS(CAST(SPLIT(client_id, '.')[OFFSET(1)] as INT)), INTERVAL 180 MINUTE) as f_unixtime,
1) парсим левую часть до точки от куки гугла значение unix времени (функция SPLIT + OFFSET)
2) далее конвертим это в INTEGER (функция CAST) делаем UNIX Epoch
3) далее вычисляем DATETIME функцией (TIMESTAMP_SECONDS)
4) далее накидываем любую TIME ZONE при желании (функция TIMESTAMP_SUB)
Как сгруппировать дату
В BigQuery мы можем группировать данные даты и времени, например за год, месяц и т.д. Группирование даты/времени означает, что вы группируете дату по определенной части даты. Например, вторник, 15 декабря 2020 года, до НЕДЕЛИ вернет первый день недели: воскресенье, 13 декабря 2020 года, до ГОДА будет среда, 1 января 2020 года, и т. д.

Используем следующие операторы:
DATE_TRUNC (date_expression, date_part)
DATETIME_TRUNC (datetime_expression, date_part)
TIMESTAMP_TRUNC (timestamp_expression, date_part)
TIME_TRUNC (time_expression, date_ part)

Поддерживаются date_part значения:
MICROSECOND - МИКРОСЕКУНДЫ
MILLISECOND - МИЛЛИСЕКУНДЫ
SECOND - СЕКУНДА
MINUTE - МИНУТА
HOUR - ЧАС
DAYOFWEEK - ДЕНЬ НЕДЕЛИ
DAY - ДЕНЬ
DAYOFYEAR - ДЕНЬ ГОДА
WEEK - НЕДЕЛЯ
WEEK(<WEEKDAY>) - НЕДЕЛЯ (<ДЕНЬ НЕДЕЛИ>)
ISOWEEK - ИСО НЕДЕЛЯ
MONTH - МЕСЯЦ
QUARTER - ЧЕТВЕРТЬ
YEAR - ГОД
ISOYEAR - ИСО ГОД

В приведенном ниже примере функция DATE_TRUNC используется для возврата первого дня месяца.

SELECT DATE_TRUNC (DATE '2022-05-19', MONTH) AS first_day_of_month
SELECT
  CAST('2022-05-26' AS DATE) AS original_date,
  DATE_TRUNC(CAST('2022-05-26' AS DATE), WEEK) AS first_day_of_week,
  DATE_TRUNC(CAST('2022-05-26' AS DATE), MONTH) AS first_day_of_month,
  DATE_TRUNC(CAST('2022-05-26' AS DATE), YEAR) AS first_day_of_year
Вместо использования функции TRUNC вы можете использовать функцию last_day, чтобы получить последний день каждой даты.

· LAST_DAY(date_expression, [date_part])
· LAST_DAY(datetime_expression, [date_part])

В приведенном ниже примере функция LAST_DAY используется для возврата последнего дня недели, начинающегося в воскресенье.

SELECT LAST_DAY (DATETIME '2022-05-25 14:30:20', WEEK(MONDAY))
SELECT
  CAST('2022-05-26' AS DATE) AS original_date,
  LAST_DAY(CAST('2022-05-26' AS DATE), WEEK) AS last_day_of_week,
  LAST_DAY(CAST('2022-05-26' AS DATE), MONTH) AS last_day_of_month,
  LAST_DAY(CAST('2022-05-26' AS DATE), YEAR) AS last_day_of_year
Как извлечь часть из даты

Вы можете извлечь часть даты из вашего значения, чтобы выполнить с ним некоторые арифметические действия, например отслеживать время входа пользователя в систему или отслеживать общую ежемесячную статистику, чтобы увидеть, в какие месяцы было больше активности.

· EXTRACT (part FROM date_expression)
· EXTRACT (part FROM datetime_expression)
· EXTRACT (part FROM timestamp_expression)
· EXTRACT (part FROM time_expression)

В приведенном ниже примере функция EXTRACT используется для возврата значения, соответствующего минутной части времени.
SELECT EXTRACT (MINUTE FROM DATETIME("2022-05-25 11:55:15")) AS minute;
Извлекаем год, номер недели и номер дня недели из нашей даты
Запрос:
SELECT
  date,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week,
  EXTRACT(DAYOFWEEK FROM date) AS weekday
FROM
  UNNEST(GENERATE_DATE_ARRAY('2021-12-22', '2022-02-07')) AS date
ORDER BY
  date ASC
Разница между EXTRACT и TRUNC

Ключевое отличие заключается в типах данных. TRUNC вернет объект DATE, DATETIME, TIMESTAMP, TIME.
EXTRACT в большинстве случаев вернет INT64

Сравнение дат

В BigQuery иногда может потребоваться сравнить дату и время для выполнения таких операций, как получение данных за предыдущую неделю или в пределах определенного диапазона с помощью различных операторов сравнения:
«<», «<=», «>», «>=», «=», «! =» или «<>», [NOT] BETWEEN, [NOT] LIKE, [NOT] IN

В приведенном ниже примере операторы "> =" и используются " <" для получения всех указанных дат, которые встречаются между обеими датами:
SELECT date
FROM
  (
    SELECT
      CAST('2022-05-10' AS DATE) AS date
    UNION ALL
(    SELECT
      CAST('2022-07-20' AS DATE) AS date)
    UNION ALL
(    SELECT
      CAST('2022-10-05' AS DATE) AS date)
    UNION ALL
(    SELECT
      CAST('2022-12-05' AS DATE) AS date)
  ) AS table_3
WHERE
  ((date >= '2021-05-10') AND (date < '2022-11-25'))
Динамическая метка времени аналог Date.Now()

Иногда нам нужно будет получать данные за последние две недели или месяц. Нам придется постоянно менять запрос, но это можно сделать с помощью оператора CURRENT
Синтаксис:
CURRENT_DATE([time_zone])
CURRENT_DATETIME([time_zone])
CURRENT_TIMESTAMP ()
CURRENT_TIME([time_zone])
Запрос:
SELECT
  CURRENT_DATE () AS current_date,
  CURRENT_DATETIME () AS current_datetime,
  CURRENT_TIMESTAMP () AS current_timestamp,
  CURRENT_TIME () AS current_time
Разница между двумя датами

Чтобы найти разницу между двумя датами, используйте _DIFF:
DATE_DIFF (date_expression_a, date_expression_b, part)
DATETIME_DIFF (datetime_expression_a, datetime_expression_b, part)
TIMESTAMP_DIFF (timestamp_expression_a, timestamp_expression_b, part)
TIME_DIFF (time_expression_a, time_expression_b, part)

В приведенном ниже примере используется функция DATE_DIFF, чтобы показать разницу в месяце между двумя датами.
SELECT
 DATE "2022-10-10" as first_date,
 DATE "2022-05-10" as second_date,
 DATE_DIFF (DATE "2022-10-10",
   DATE "2022-05-10", MONTH) as month_difference;
Например, мы хотим отфильтровать наши данные за последние 10 месяцев. Для этого нам нужно сравнить наш столбец (current_date) и убедимся, что разница не превышает 10 месяцев.
WHERE date_diff(current_date(),date,DAY) <= X
SELECT
  date
FROM
  (
    SELECT
      CAST('2022-05-06' AS DATE) AS date
    UNION ALL
(    SELECT
      CAST('2022-09-08' AS DATE) AS date)
    UNION ALL
(    SELECT
      CAST('2021-03-02' AS DATE) AS date)
  ) AS table_1
WHERE
  (DATE_DIFF(CURRENT_DATE(), date, MONTH) < 10)
Применение

Создание библиотеки дат
Для того чтобы создать библиотеку дат напишем простой запрос:
SELECT *
  FROM UNNEST (GENERATE_DATE_ARRAY ('2022-05-01', '2022-06-01', INTERVAL 1 DAY)) AS date
Запрос создаст столбец, в котором по возрастанию будут идти даты промежуток которых мы указали в запросе.
Можно создать подробную библиотеку дат с разбивкой основной даты на: годы, номер месяца, название месяца, номер дня, номер дня недели, номер недели, номер дня года. Это нужно для построения когортного анализа.
SELECT
         MY_DATE
        ,EXTRACT (YEAR FROM MY_DATE) as YEAR
        ,EXTRACT (MONTH FROM MY_DATE) as MONTH_NUMBER
        ,FORMAT_DATETIME ("%B", DATETIME(MY_DATE)) as MONTH
        ,EXTRACT (DAY FROM MY_DATE) as DAY_NUMBER
        ,EXTRACT (DAYOFWEEK FROM MY_DATE) as DAY_OF_WEEK
        ,EXTRACT (WEEK FROM MY_DATE) as WEEK_NUMBER
        ,EXTRACT (DAYOFYEAR FROM MY_DATE) as DAY_OF_YEAR
    FROM 
    (
    SELECT DATE_ADD ('2022-01-01', INTERVAL param DAY) AS MY_DATE
      FROM unnest (GENERATE_ARRAY (0, 1000, 1)) as param
    )
Извлекаем из даты подробное значения. Библиотека будет начинаться с 2022-01-01 и иметь 1000 строк, то есть до даты 2024-09-27
Работа с таблицами в которых каждая отдельная дата это отдельная таблица

Например, хотим посчитать количество пользователей с уникальным user_id и которые пришли на сайт в первый раз с 2022-03-01 по 22-05-20
SELECT count (distinct user_id) as count_user_id
  from `fjshd-c61fd.analytics_263458172.events_*`
    where event_name = 'first_visit'
      and _TABLE_SUFFIX between '20220301' and '20220520'
Если хотим выгружать данные, например, в системы визуализации, то нам нужно выгружать данные за предыдущий день, чтобы быть уверенным что собраны все данные за день. Или если использовать расписание с добавлением данных в общую таблицу.
WHERE
  _TABLE_SUFFIX = FORMAT_DATE ('%Y%m%d', DATE_SUB (CURRENT_DATE (), INTERVAL 1 DAY))
Made on
Tilda