Работа с 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_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 (expression AS datatype) Ниже приведены несколько примеров использования функции

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

Усложним задачу, нам нужно вычесть 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 метки из куки гугла

1) парсим левую часть до точки от куки гугла значение unix времени (функция SPLIT + OFFSET) 2) далее конвертим это в INTEGER (функция CAST) делаем UNIX Epoch 3) далее вычисляем DATETIME функцией (TIMESTAMP_SECONDS) 4) далее накидываем любую TIME ZONE при желании (функция TIMESTAMP_SUB)

SELECT 
TIMESTAMP_SUB(TIMESTAMP_SECONDS(CAST(SPLIT(client_id, '.')[OFFSET(1)] as INT)), INTERVAL 180 MINUTE) as f_unixtime,

Как сгруппировать дату В 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

Вместо использования функции 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))

Как извлечь часть из даты Вы можете извлечь часть даты из вашего значения, чтобы выполнить с ним некоторые арифметические действия, например отслеживать время входа пользователя в систему или отслеживать общую ежемесячную статистику, чтобы увидеть, в какие месяцы было больше активности. · 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

Динамическая метка времени аналог 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)

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)

Применение Создание библиотеки дат Для того чтобы создать библиотеку дат напишем простой запрос:

Запрос создаст столбец, в котором по возрастанию будут идти даты промежуток которых мы указали в запросе.

SELECT *
  FROM UNNEST (GENERATE_DATE_ARRAY ('2022-05-01', '2022-06-01', INTERVAL 1 DAY)) AS date

Можно создать подробную библиотеку дат с разбивкой основной даты на: годы, номер месяца, название месяца, номер дня, номер дня недели, номер недели, номер дня года. Это нужно для построения когортного анализа.

Извлекаем из даты подробное значения. Библиотека будет начинаться с 2022-01-01 и иметь 1000 строк, то есть до даты 2024-09-27

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
    )

Работа с таблицами в которых каждая отдельная дата это отдельная таблица Например, хотим посчитать количество пользователей с уникальным 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))