Памятка по распространенным выражениям даты и времени BigQuery

Сегодня утром я только начал работать над проектом прогнозирования временных рядов. Первым шагом в этом проекте, как и в большинстве задач в области науки о данных, был сбор данных. Это означало относительно простой SQL-запрос в Google BigQuery. Я снова обнаружил, что гуглил, как извлечь год из временной метки, и пролистывал документацию, чтобы найти правильную функцию, и понял, что мне нужно где-то это записать. Я пишу это в блоге, чтобы мне было к чему вернуться, и, надеюсь, это также может помочь другим. Обратите внимание, что все выражения основаны на стандартном SQL.

Даты

Части даты

Список всех частей даты, которые можно использовать в выражениях, перечисленных ниже:

DAYOFWEEK (returns 1-7 Sunday is 1)
DAY
DAYOFYEAR (0-365)
WEEK (week of year 0-53, week begins on Sunday)
WEEK(<chosen weekday>) (week of year begins on your chosen day e.g. SUNDAY)
ISOWEEK (ISO 8601 week number, week begins on Monday)
MONTH
QUARTER (1-4)
YEAR (ISO 8601 year number)

Извлечь часть даты

EXTRACT(part FROM date_expression)
Example: EXTRACT(YEAR FROM 2019-04-01)
Output: 2019

Постройте дату из целых чисел

DATE(year, month, day)
Example: DATE(2019, 04, 01)
Output: 2019-04-01

Сложение и вычитание из дат

DATE_ADD(date_expression, INTERVAL INT64_expr date_part)
Example: DATE_ADD('2019-04-01', INTERVAL 1 DAY)
Output: 2019-04-02
DATE_SUB(date_expression, INTERVAL INT64_expr date_part)
Example: DATE_SUB('2019-04-01', INTERVAL 1 DAY)
Output: 2019-03-31
Example use case - dynamic dates:
where my_date between DATE_SUB(current_date, INTERVAL 7 DAY) and DATE_SUB(current_date, INTERVAL 1 DAY)

Разница между двумя датами

DATE_DIFF(date_expression, date_expression, date_part)
Example: DATE_DIFF(2019-02-02, 2019-02-01, DAY)
Output: 1

Укажите степень детализации даты

DATE_TRUNC(date_expression, date_part)
Example: DATE_TRUNC(2019-04-12, WEEK)
Output: 2019-04-07

Раз

Части времени

MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR

Создайте объект datetime из целых чисел

DATETIME(year, month, day, hour, minute, second)
DATETIME(date_expression, time_expression)
DATETIME(timestamp_expression [, timezone])
Example: DATETIME(2019, 04, 01, 11, 55, 00)
Output: 2019-04-01 11:55:00

Сложить и вычесть время

DATETIME_ADD(datetime_expression, INTERVAL INT64_expr part)
Example: DATETIME_ADD('2019-04-01 11:55:00', INTERVAL 1 MINUTE)
Output: 2019-04-01 11:56:00
DATETIME_SUB(datetime_expression, INTERVAL INT64_expr part)
Example: DATETIME_SUB('2019-04-01 11:55:00', INTERVAL 1 MINUTE)
Output: 2019-04-01 11:54:00

Разница между двумя временами

DATETIME_DIFF(datetime_expression, datetime_expression, part)
Example: DATETIME_DIFF('2019-04-01 11:56:00', '2019-04-01 11:55:00', MINUTE)
Output: 1

Укажите степень детализации времени

DATETIME_TRUNC(datetime_expression, part)
Example: DATETIME_TRUNC('2019-04-01 11:55:00', HOUR)
Output: 2019-04-01 11:00:00

Это ни в коем случае не исчерпывающее руководство по выражениям даты и времени в BigQuery. Он предназначен только для краткого ознакомления с теми, которые я использую чаще всего. Более подробное руководство можно найти в документации Google BigQuery.