Операторы и функции для работы с датами

Бизнес-процессы тесно увязаны со временем.

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

Две основные функции для работы со временем и датой — EXTRACT (англ. «извлекать») и DATE_TRUNC (от англ. truncate date, «усекать, отбрасывать дату»). Обе функции вызывают в блоке SELECT.

Шаблон функции EXTRACT:

Скопировать кодSQL
    SELECT
      EXTRACT(часть_даты FROM столбец) AS новый_столбец_с_датой 
    FROM 
        Таблица_со_всеми_датами;

Название функции определяет её суть. EXTRACT извлекает из даты нужную часть: год, месяц, минуту. Что ещё можно получить вызовом EXTRACT:

century — век;
day — день;
doy (от англ. day of the year) — день года: от 1 до 365/366;
isodow (от англ. day of the week и ISO 8601, международного стандарта даты и времени) — день недели: понедельник — 1, воскресенье — 7.
hour — час;
milliseconds — миллисекунда;
minute — минута;
second — секунда;
month — месяц;
quarter — квартал;
week — неделя в году;
year — год.

Вызовем функцию EXTRACT и получим из поля log_on таблицы user_activity (англ. «активность пользователей») два столбца — с месяцем и днём входа определённого пользователя в личный кабинет:

id_user	log_on	log_off
6	2019-03-01 23:34:55	2019-04-01 01:20:45
156	2019-07-03 17:59:21	2019-07-03 19:31:34
65	2019-03-25 14:30:46	2019-03-25 17:47:53

Скопировать кодSQL
SELECT 
    id_user,
    EXTRACT(MONTH FROM log_on) AS month_activity,
    EXTRACT(DAY FROM log_on) AS day_activity
FROM 
    user_activity;

user_activity	month_activity	day_activity
6	3	1
156	7	3
65	3	25

Пользователь под номером 6 входил в личный кабинет первого числа третьего месяца. А 156-ой пользователь — третьего числа седьмого месяца.

DATE_TRUNC усекает дату до часа, дня или месяца. В отличие от EXTRACT часть, до которой нужно усечь дату, записывают как строку. А столбец, откуда берут данные о времени, указывают через запятую:

Скопировать кодSQL
SELECT
    DATE_TRUNC('часть_даты_до_которой_усекаем', столбец) AS новый_столбец_с_датой 
FROM 
    Таблица_со_всеми_датами;

Часть даты, до которой данные нужно «обнулить», указывают в аргументе функции DATE_TRUNC:

'microseconds' — микросекунды;
'milliseconds' — миллисекунды;
'second' — секунда;
'minute' — минута;
'hour' — час;
'day' — день;
'week' — неделя;
'month' — месяц;
'quarter' — квартал;
'year' — год;
'decade' — декада года;
'century' — век.

Знать точное время активности пользователя бывает важно. Однако, если нужно сгруппировать данные по дате и часу входа, минуты и секунды портят картину. Усечём значения поля log_on до часов.

Скопировать кодSQL
SELECT 
    DATE_TRUNC('hour',log_on) as date_log_on
FROM 
    user_activity;

Скопировать кодSQL
date_log_on

2019-03-01 23:00:00

2019-07-03 17:00:00

2019-03-25 14:00:00