SQL с нуля. Часть 8: Дата и время — DATE_TRUNC, EXTRACT, INTERVAL

Это часть 8 из 10 учебника «SQL с нуля для аналитика». Содержание серии в конце поста. ← Часть 7

TL;DR: DATE_TRUNC('month', col) округляет timestamp до начала месяца. EXTRACT(YEAR FROM col) извлекает год. col + INTERVAL '1 day' — арифметика. TO_CHAR(col, 'YYYY-MM') — форматирование в строку. Часовые пояса через AT TIME ZONE. 90% аналитических запросов используют DATE_TRUNC для группировки.

В этой части:

• DATE_TRUNC для группировки по месяцам/неделям/дням
• EXTRACT для года/месяца/дня недели
• Арифметика дат через INTERVAL
• Часовые пояса (UTC, Moscow)
• TO_CHAR и TO_DATE

Как сгруппировать данные по месяцам?

-- Выручка по месяцам
SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

Результат: для всех заказов мая → 2026-05-01 00:00:00. Можно группировать.

Доступные единицы: year, quarter, month, week, day, hour, minute, second.

Типичная метрика: MoM revenue. SELECT DATE_TRUNC('month', created_at), SUM(amount) GROUP BY 1 ORDER BY 1 — даёт график выручки по месяцам, основа для всех executive дашбордов.

Как извлечь часть даты?

SELECT
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DAY FROM created_at) AS day,
    EXTRACT(DOW FROM created_at) AS day_of_week,   -- 0=воскресенье, 1=понедельник
    EXTRACT(WEEK FROM created_at) AS week_number
FROM orders;

В PG также есть короткие функции: YEAR(col) не работает, но EXTRACT(YEAR FROM col) или date_part('year', col) — да.

В ClickHouse: toYear(col), toMonth(col) — короче.

Как сделать арифметику с датами?

Используй INTERVAL:

-- Заказы за последние 7 дней
SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days';

-- Дата истечения подписки = дата начала + 30 дней
SELECT subscription_start + INTERVAL '30 days' AS expiry FROM subs;

-- Разница в днях
SELECT
    end_date - start_date AS duration_days
FROM contracts;

Как работать с часовыми поясами?

В PG два типа: TIMESTAMP (без TZ) и TIMESTAMPTZ (с TZ).

Правило: всегда храни в UTC (TIMESTAMPTZ), конвертируй для отображения:

-- Хранится в UTC, отображаем в МСК
SELECT
    user_id,
    created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Moscow' AS msk_time
FROM orders;

Типичный баг: events приходят в UTC, отчёт «продажи 3 ноября» считается по локальной дате. События с 21:00 UTC попадают в следующий день МСК. Фикс: всегда конвертируй UTC → MSK через AT TIME ZONE перед DATE_TRUNC.

Как отформатировать дату в строку?

SELECT
    TO_CHAR(created_at, 'YYYY-MM-DD') AS date_str,         -- '2026-06-02'
    TO_CHAR(created_at, 'DD.MM.YYYY') AS rus_date,         -- '02.06.2026'
    TO_CHAR(created_at, 'YYYY-Mon') AS year_mon,           -- '2026-Jun'
    TO_CHAR(created_at, 'HH24:MI') AS hour_min,            -- '14:32'
    TO_CHAR(created_at, 'Day, DD Month') AS full           -- 'Tuesday, 02 June'
FROM orders;

Обратное — TO_DATE(string, format) или TO_TIMESTAMP(string, format).

Какие 6 типичных запросов аналитика с датой?

Выручка по месяцам

SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount)
FROM orders GROUP BY month ORDER BY month;

DAU (Daily Active Users)

SELECT DATE_TRUNC('day', event_ts) AS day, COUNT(DISTINCT user_id) AS dau
FROM events GROUP BY day;

Cohort retention (упрощённо)

SELECT
    DATE_TRUNC('month', signup_at) AS cohort,
    DATE_TRUNC('month', event_ts) AS event_month,
    COUNT(DISTINCT user_id) AS users
FROM events
GROUP BY cohort, event_month;

Подробнее — в Cohort retention SQL.

Last 7 days vs Previous 7 days

SELECT
    SUM(CASE WHEN created_at >= NOW() - INTERVAL '7 days' THEN amount END) AS last_7,
    SUM(CASE WHEN created_at >= NOW() - INTERVAL '14 days'
              AND created_at < NOW() - INTERVAL '7 days' THEN amount END) AS prev_7
FROM orders;

Возраст пользователя

SELECT
    user_id,
    EXTRACT(YEAR FROM AGE(NOW(), birthday)) AS age_years
FROM users;

День недели заказа

SELECT
    EXTRACT(DOW FROM created_at) AS dow,
    COUNT(*) AS orders
FROM orders
GROUP BY dow;

Топ-3: (1) DAU = COUNT(DISTINCT user_id) GROUP BY DATE(event_ts), (2) MoM revenue, (3) cohort retention по месяцу регистрации.

Какие 4 ошибки с датами?

• Ошибка 1: WHERE created_at > '2026-06-02' для отчёта «за июнь» — включит только данные после 2 июня. Используй DATE_TRUNC('month', created_at) = '2026-06-01'.
• Ошибка 2: Игнор часовых поясов. '2026-06-02 23:30 UTC' — это 3 июня в Москве. Конвертируй или храни всё в UTC.
• Ошибка 3: Сравнение TIMESTAMP с DATE без CAST — поведение зависит от диалекта.
• Ошибка 4: BETWEEN '2026-06-01' AND '2026-06-30' — для timestamp не включит 30 июня вечером. Используй >= и <.

Частые вопросы про дату и время

DATE_TRUNC или EXTRACT для группировки по месяцам?

DATE_TRUNC. Он возвращает timestamp (2026-05-01), что даёт правильную сортировку и формат.

Что такое NOW(), CURRENT_DATE, CURRENT_TIMESTAMP?

• NOW() — текущий момент (timestamptz)
• CURRENT_DATE — сегодня (date)
• CURRENT_TIMESTAMP — то же что NOW()

В ClickHouse функции по-другому называются?

Да: toStartOfMonth(col) вместо DATE_TRUNC, toYear(col) вместо EXTRACT. Но логика та же.

Как считать рабочие дни между датами?

В PG нет встроенной функции. Через generate_series + EXTRACT(DOW) и фильтр выходных. Или таблица праздников.

Как получить первый день текущего месяца?

Что дальше?

В Части 9 — работа со строками: LIKE, ILIKE, SUBSTRING, regex.

В Pro — безлимит мок-собесов на AI-интервью + 491 SQL-задача + 612 тестовых заданий + 50+ блог-постов.

Навигация по учебнику

← Часть 7 | Часть 8: Дата и время | Часть 9 →

Содержание серии: 1 · 2 · 3 · 4 · 5 · 6 · 7 · 8 · 9 · 10

← Вернуться к оглавлению

Источники

• PostgreSQL Docs: «Date/Time Functions» (postgresql.org/docs/current/functions-datetime.html)
• PostgreSQL Docs: «Date/Time Types» (postgresql.org/docs/current/datatype-datetime.html)