SQL даты и время в аналитике: периоды, недели и календарь дат

Коротко

Для периода используйте левую включённую и правую исключённую границу: date >= start и date < next_start. Так запрос не зависит от количества часов, не дублирует события между соседними периодами и не смешивает полный день с текущим.

• date_trunc округляет дату до начала дня, недели или месяца.
• Для июня границы — 2026-06-01 включительно и 2026-07-01 исключительно.
• Таймзона должна быть частью определения метрики.
• Неполный текущий день сравнивайте отдельно или исключайте из истории.
• Возраст пользователя — разница между событием и датой старта.

Полный месяц через две границы

Для июня не нужно вычислять «последнюю секунду месяца». Следующий месяц уже является естественной правой границей. Это работает одинаково для DATE и TIMESTAMP и не создаёт проблем с миллисекундами. На снимке данных вместо current_date подставляйте дату последнего, неполного дня выгрузки — в учебной базе date '2026-08-30': окно от сегодняшней даты там пусто.

select
  count(*) as events_count,
  count(distinct user_id) as active_users
from events
where event_time >= timestamp '2026-06-01 00:00:00'
  and event_time < timestamp '2026-07-01 00:00:00';

-- учебная база: 6018 событий, 1042 активных пользователя

BETWEEN включает обе границы, и на timestamp это стоит одного дня

Полуоткрытый интервал из предыдущего раздела выглядит многословно, поэтому его регулярно сокращают до event_time between date '2026-07-01' and date '2026-07-31'. Условие читается как «весь июль», а выполняется иначе. BETWEEN включает обе границы, и правая граница здесь — не «конец 31 июля», а значение date '2026-07-31', которое при сравнении с timestamp превращается в 2026-07-31 00:00:00. В выборку попадают только те события, которые записаны ровно в полночь.

На учебной базе в июле 12 752 события, а BETWEEN возвращает 12 174. Все 578 событий 31 июля в отчёт не попали: ровно в полночь не записано ни одного. Месяц просел на 4,5%. Цифра слишком маленькая, чтобы вызвать тревогу, и слишком большая, чтобы месяц сошёлся с соседним отчётом.

Опасность в том, что ошибка появляется не в момент написания запроса. Пока колонка имеет тип DATE, BETWEEN честно включает последний день, и запрос правильный: payments.paid_at в учебной базе — DATE, и оба фильтра дают 442 оплаты. Потом в схему добавляют время оплаты, тип меняют на timestamptz, старые отчёты продолжают выполняться — и каждый начинает терять свой последний день. Проверять придётся все запросы с BETWEEN по этой колонке, а не один.

Первое, что обычно предлагают как исправление, — привести колонку к дате: cast(event_time as date) between date '2026-07-01' and date '2026-07-31'. Включительность здесь честная, все 12 752 события попадут в июль, и для разового расследования так можно. В регулярном отчёте у этой формы две цены. Функция от колонки мешает базе использовать индекс по event_time, поэтому вместо чтения диапазона получится полный проход по таблице. А у колонки timestamptz приведение к дате выполняется в часовом поясе сессии, то есть один и тот же запрос у двух аналитиков с разными настройками вернёт разные числа на границах суток.

Ещё одна тихая деталь: BETWEEN требует, чтобы левая граница была не больше правой. Если параметры подставляются шаблоном и приехали в обратном порядке, запрос не падает, а возвращает ноль строк. Пустой отчёт выглядит как «в этом периоде ничего не было», и именно так его обычно и трактуют.

select
  'events.event_time, TIMESTAMP' as column_name,
  count(*) filter (
    where event_time between date '2026-07-01' and date '2026-07-31'
  ) as rows_between,
  count(*) filter (
    where event_time >= date '2026-07-01'
      and event_time <  date '2026-08-01'
  ) as rows_half_open
from events
union all
select
  'payments.paid_at, DATE',
  count(*) filter (
    where paid_at between date '2026-07-01' and date '2026-07-31'
  ),
  count(*) filter (
    where paid_at >= date '2026-07-01'
      and paid_at <  date '2026-08-01'
  )
from payments
order by column_name;

• Для чисел и для колонок типа DATE BETWEEN уместен: границы там действительно включительные.
• Для timestamp пишите >= start and < next_start — правая граница тогда не зависит от точности поля.
• Границы держите параметрами запроса, а не руками внутри текста: подстановка next_start считается один раз.
• Пустой результат проверяйте отдельно: он может означать перевёрнутый интервал, а не отсутствие событий.

date_trunc: собрать данные по календарному периоду

date_trunc приводит timestamp к началу выбранного периода. Сначала выберите таймзону продукта, потом округляйте дату и группируйте результат. Если данные уже имеют тип DATE, достаточно группировать по самой колонке. date_trunc возвращает метку времени (DuckDB до версии 1.5 — дату), поэтому в запросе результат приведён к DATE.

Здесь date_trunc нужен только как способ задать границы периода. Остальные функции даты — текущая дата, extract, разница дат, форматирование и различия диалектов — собраны в отдельном справочнике по ссылке под разделом.

select
  cast(date_trunc('week', event_time) as date) as week_start,
  count(distinct user_id) as active_users
from events
where event_time >= timestamp '2026-06-01 00:00:00'
  and event_time < timestamp '2026-07-01 00:00:00'
group by week_start
order by week_start;

Неделя в date_trunc начинается в понедельник, и последняя неделя месяца обрезана

У date_trunc('week', ...) нет настройки первого дня: PostgreSQL и DuckDB всегда отсчитывают неделю по ISO-правилу, от понедельника. Это не опция сессии и не зависит от локали. Если бизнес смотрит недели с воскресенья — а так считают многие западные BI-панели и часть рекламных кабинетов, — два отчёта над одной таблицей дадут разные недельные значения, и оба будут технически правильными. На учебной базе недели с понедельника дают 196, 379, 581, 747 и 944 активных пользователя, с воскресенья — 182, 369, 566, 744 и 934. Сначала выясните, какое правило зафиксировано в словаре метрик, и только потом выбирайте функцию.

Когда нужна неделя не с понедельника, не сдвигайте результат на день после группировки: смещать надо вход. Прибавьте к дате смещение, обрежьте до недели и отнимите смещение обратно — начало недели сдвинется, а распределение событий по корзинам останется верным. В PostgreSQL 14+ ту же задачу решает date_bin, которому задают шаг и точку отсчёта: он умеет и недели с произвольного дня, и четырёхчасовые корзины для внутридневных графиков. В DuckDB его роль играет time_bucket.

Вторая ловушка живёт в границах фильтра. Запрос из предыдущего раздела берёт июнь целиком: 1 июня 2026 года — понедельник, поэтому первая корзина полная. А вот последняя неделя начинается 29 июня и обрезается фильтром на 30 июня — в ней два дня вместо семи. Недели июня растут до 747 активных пользователей, а последняя корзина даёт 401: график падает почти вдвое, хотя в полной неделе 944 человека. Команда видит «обвал на неделе», которого нет.

Лечится это двумя способами, и выбор зависит от вопроса. Если нужен ровно июнь, отбрасывайте неполные недели и пишите в подписи, что период обрезан. Если нужен недельный ритм, расширьте границы до начала недели, в которую попадает первое число, и до начала недели после последнего — тогда каждая корзина содержит семь дней, но выйдет за пределы месяца. Третьего варианта, в котором и месяц ровный, и недели полные, не существует: 30 дней не делятся на 7.

-- от понедельника недели, где лежит 1 июня, до понедельника после недели, где лежит 30 июня
select
  cast(date_trunc('week', event_time) as date) as week_start,
  count(distinct user_id)                      as active_users
from events
where event_time >= date_trunc('week', timestamp '2026-06-01 00:00:00')
  and event_time <  date_trunc('week', timestamp '2026-06-30 00:00:00') + interval '7 days'
group by week_start
order by week_start;

-- недели с воскресенья, которые покрывают июнь: с 31 мая по 4 июля
select
  cast(date_trunc('week', event_time + interval '1 day') - interval '1 day' as date) as week_start,
  count(distinct user_id)                                                           as active_users
from events
where event_time >= timestamp '2026-05-31 00:00:00'
  and event_time <  timestamp '2026-07-05 00:00:00'
group by week_start
order by week_start;

Календарный период и rolling window

Календарный месяц отвечает на вопрос «что произошло в июне». Rolling window отвечает на вопрос «что происходило за последние 30 дней на каждую дату». Нельзя подменять один подход другим: rolling-график сглаживает границы месяцев, но может скрыть сезонный перелом.

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

Для retention, activation и LTV нужно знать, сколько дней прошло от старта пользователя до события. В учебной базе старт — signup_date, а событие — любая строка events. Разница дат создаёт возраст когорты, который потом можно использовать в D1, D7 или D30.

В возрасте 1 день активны 2 954 пользователя, в возрасте 7 дней — 881. Это числители retention; знаменатель — только те, у кого этот день уже наступил.

with ages as (
  select distinct
    u.user_id,
    cast(e.event_time as date) - u.signup_date as days_since_signup
  from users as u
  join events as e on e.user_id = u.user_id
  where e.event_time < timestamp '2026-08-30 00:00:00'  -- 30 августа — неполный день выгрузки
)
select
  days_since_signup,
  count(*) as users
from ages
where days_since_signup <= 7
group by days_since_signup
order by days_since_signup;

• D0 — событие в день старта.
• D1 — событие на следующий календарный день.
• D7 — событие через семь календарных дней.
• Rolling 24 hours может отличаться от календарного D1.

Пауза между событиями: воронка не показывает, сколько человек ждал

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

Считается такая пауза оконной функцией lag: она достаёт timestamp предыдущей строки в пределах одного пользователя при заданном порядке сортировки. Разница двух timestamp даёт interval; для агрегаций его удобнее сразу перевести в секунды через extract(epoch from ...), иначе перцентиль в PostgreSQL останется интервалом, а DuckDB его не посчитает.

Распределение таких пауз почти всегда с длинным хвостом, поэтому среднее здесь бесполезно. В учебной базе шагов оплаты нет, и запрос меряет паузу между соседними событиями пользователя: 30 728 пауз, p50 — 47 часов, p75 — 121, p90 — 260, p95 — 361. Медиана — двое суток, а каждая двадцатая пауза длиннее пятнадцати дней. Среднее — 95,8 часа, вдвое больше медианы, и не описывает ни одну группу: для половины пауз оно сильно завышено, для хвоста — занижено.

У интервалов есть два источника мусора. Первый — повторные попытки: событие вроде payment_retry между началом и успехом создаёт серию коротких пауз, и если не отделить номер попытки, медиана поедет вниз. Второй — порядок сортировки. Когда два события пришли с одинаковым timestamp, база выбирает их порядок произвольно, а пауза между ними равна нулю. Добавьте в order by вторичный ключ — event_id или порядковый номер в сессии, — и проверьте запросом, сколько нулевых пауз в результате: в учебной базе их нет.

Границы сценария тоже нужно задать явно. Окно partition by user_id сцепляет вчерашний заход с сегодняшним: 47 часов из учебной базы — время между визитами, а не ожидание внутри сеанса. Общая медиана к тому же смешивает разные переходы: с условием event_name = 'invite_sent' в подзапросе gaps медиана 1 135 пауз перед приглашением — 5 часов. Сессий в базе нет; когда они есть, добавляйте идентификатор сессии или заказа в partition by: события должны принадлежать одной истории.

with steps as (
  select
    event_name,
    event_time,
    lag(event_time) over (
      partition by user_id
      order by event_time, event_id
    ) as prev_at
  from events
)
select
  count(*) as transitions,
  percentile_cont(0.5)  within group (order by gap_hours) as p50,
  percentile_cont(0.75) within group (order by gap_hours) as p75,
  percentile_cont(0.9)  within group (order by gap_hours) as p90,
  percentile_cont(0.95) within group (order by gap_hours) as p95,
  round(cast(avg(gap_hours) as numeric), 1) as avg_hours,
  count(*) filter (where gap_hours <= 0) as zero_gaps
from (
  select extract(epoch from event_time - prev_at) / 3600 as gap_hours
  from steps
  where prev_at is not null  -- пауза перед одним шагом: добавьте and event_name = 'invite_sent'
) as gaps;

Если в данных есть timestamp и таймзона

Событие в 23:30 UTC может быть уже следующим днём для пользователя в Москве. Поэтому сначала переведите timestamp в бизнесовую таймзону, а только потом применяйте date_trunc или приводите значение к DATE. Таймзона — часть определения метрики, а не косметика формата.

В учебной базе event_time — метка без пояса. Если считать её UTC, московский день сменится у 281 события из 35 341 — у всех с 21:00.

select
  count(*) as events_count,
  count(*) filter (
    where cast(event_time at time zone 'UTC' at time zone 'Europe/Moscow' as date)
       <> cast(event_time as date)
  ) as moved_to_next_day
from events;

Частые ошибки с датами

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

• Использовать <= last_day 23:59:59 и потерять события с миллисекундами.
• Включить сегодняшний неполный день в сравнение с полными днями.
• Считать неделю как последние семь дней, хотя отчёт построен по календарным неделям.
• Применить таймзону после группировки, а не до неё.
• Сравнить когорты, которым доступны разные возраста D7 или D30.
• Фильтровать timestamp строковым значением без явного типа и границы.

Часовой пояс: преобразуйте до группировки

От часовой зоны зависит не только дата отдельного события, но и метрика: DAU, revenue, распределение активности по часам. Если считать метки учебной базы UTC, 1 июля по Москве — 282 активных пользователя, по дате UTC — 285. Границы фильтра сдвигаются вместе с днём: июль по Москве начинается в 21:00 UTC 30 июня.

Не смешивайте timezone-aware и naive значения. В одном запросе явно укажите, что приходит из источника и в какой зоне строится бизнес-день. Для повторяемого отчёта вынесите это правило в view или документ определения метрики, чтобы разные аналитики не использовали разные AT TIME ZONE.

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

select
  cast(event_time at time zone 'UTC'
    at time zone 'Europe/Moscow' as date) as business_date,
  count(distinct user_id) as dau
from events
where event_time >= timestamp '2026-06-30 21:00:00'  -- июль по Москве в метках UTC
  and event_time < timestamp '2026-07-31 21:00:00'
group by business_date
order by business_date;

Неполный период и календарная сетка

В отчёте нужно различать отсутствие событий и отсутствие строки. Если 5 июля в продукте действительно не было ни одного события, это ноль. Если загрузка за 5 июля ещё не приехала, это NULL или статус incomplete. Создайте календарную сетку и присоедините агрегат к ней, чтобы пропущенные даты были видны явно. В учебной базе пустых дней нет, поэтому календарь в запросе выходит за конец выгрузки: с 31 августа по 2 сентября — no_row, а не ноль.

Текущий день не сравнивайте с полным вчерашним днём без поправки. 30 августа там же — 223 активных против 530 накануне: выгрузка обрывается в 12:59. Для активности внутри дня можно показывать same-hour comparison: сегодня до 14:00 против предыдущих дней до 14:00. Для месячного отчёта используйте только завершённые месяцы или маркируйте текущий как draft.

Количество строк календаря тоже проверяйте. Если ожидалось 30 дней, а после JOIN осталось 29, причина может быть в фильтре или часовом поясе. Календарная таблица помогает сделать пропуск видимым, но не решает его автоматически.

with calendar as (
  select cast(g.day as date) as day
  from generate_series(
    date '2026-08-24',
    date '2026-09-02',
    interval '1 day'
  ) as g(day)
), daily as (
  select cast(event_time as date) as day, count(distinct user_id) as dau
  from events
  group by cast(event_time as date)
)
select
  c.day,
  d.dau,
  case when d.day is null then 'no_row' else 'observed' end as state
from calendar as c
left join daily as d on d.day = c.day
order by c.day;

Календарь дат меняет знаменатель среднего, а не только вид графика

Запрос из предыдущего раздела делает пропуски видимыми, и на этом обычно останавливаются. Но сетка влияет не только на картинку: она задаёт знаменатель для всего, что считается «в среднем за день». Возьмите оплаты учебной базы за неделю с 8 по 14 июня: 3, 0, 2, 0, 5, 7, 5 — всего 22 оплаты. Группировка по paid_at вернёт пять строк, и среднее выйдет 4,4. По календарю из семи дней среднее равно 3,14. Разница в 1,4 раза появляется из-за того, какие дни попали в знаменатель, а не из-за данных.

Та же подмена живёт в графиках с линией: BI-инструмент соединит 2 и 5 прямой и нарисует плавный рост, хотя между ними был день без единой оплаты. На столбиках пропуск заметнее, но ряд всё равно сжимается — пять столбцов вместо семи, и ритм недели читается неверно.

Строится сетка через generate_series, но у неё есть правило, которое ломают чаще остальных: календарь должен быть слева в left join, а все условия по фактам — внутри подзапроса с агрегацией, а не в where внешнего запроса. Достаточно добавить снаружи where x.plan = 'team' — и строки календаря без оплат отфильтруются, потому что plan у них NULL. left join молча превратится в inner join, и сетка пропадёт.

Когда разрез не один, календаря мало: нужно декартово произведение дат и значений разреза. В учебной базе три тарифа, и в ту же неделю оплат team не было пять дней из семи: без cross join эти дни у тарифа просто не появятся в результате, и из 21 строки останется 10. Для месяцев и недель сетка строится тем же generate_series с шагом 1 month или 7 days — и для месячных отчётов это заодно страхует от пропущенного месяца в истории.

Последнее: сетку стоит строить не «до сегодня», а до последней завершённой даты. Иначе в ряду каждый день будет появляться неполный хвост, который визуально читается как падение. Правило из раздела о неполном периоде распространяется и на календарь: сетка заканчивается там же, где заканчиваются надёжные данные.

with days as (
  select cast(g.day as date) as day
  from generate_series(date '2026-06-08', date '2026-06-14', interval '1 day') as g(day)
), plans as (
  select distinct plan from payments
), daily as (
  select paid_at as day, plan, count(*) as payments_count
  from payments
  where paid_at >= date '2026-06-08'
    and paid_at <  date '2026-06-15'
  group by paid_at, plan
)
select
  d.day,
  p.plan,
  coalesce(x.payments_count, 0) as payments_count
from days as d
cross join plans as p
left join daily as x on x.day = d.day and x.plan = p.plan
order by d.day, p.plan;

• Среднее за период считайте по числу календарных дней, если метрика описывает дни, а не события.
• Фильтры по фактам держите внутри подзапроса: снаружи они убивают left join.
• Для разрезов добавляйте cross join со списком значений, иначе сегмент с нулём исчезнет.
• Ноль и «данных ещё нет» оставляйте разными значениями: coalesce(..., 0) применяйте только к первому.

Инкрементальная загрузка: watermark, окно пересчёта и опоздавшие строки

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

Граница, до которой пайплайн довёл обработку, называется watermark. Ключевое решение — по какому полю её хранить. business_date отвечает на вопрос «когда событие произошло», ingested_at — «когда строка приехала в хранилище». Это разные колонки, и подменять первую второй нельзя. Событие от 7 июля, доставленное 9-го, при фильтре business_date > watermark не попадёт ни в один запуск: его дата уже пройдена. Строка есть в источнике, в витрине её нет, и обнаружится это через месяц при сверке.

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

В учебной базе времени поступления нет, и запрос задаёт его сам: каждое двадцатое событие 25 августа приезжает на трое суток позже, прошлый запуск закончился в полночь 28-го. Фильтр по дате события взял бы 28–30 августа и оставил 25-е без 31 строки из 613. Фильтр по времени поступления находит эту дату, окно добавляет 27-е, и все пять дней пересчитываются целиком. Запрос — читающая половина замены: песочница выполняет только SELECT.

Перезапись обязана быть идемпотентной. insert по окну при повторном запуске удвоит строки в пересчитанных днях, и вместо недосчёта получится перекос вверх. Рабочая схема — в одной транзакции удалить затронутые даты, вставить вместо них результат запроса и сдвинуть watermark до max(ingested_at); либо заменить партицию, либо merge по ключу факта. Проверяйте результат до публикации: сравните число строк и сумму метрики по каждому дню окна с тем, что было до запуска. Изменение на несколько процентов — нормальная досылка, изменение в разы — повод остановиться.

Отдельно держите в голове исправления задним числом. Если в источнике строку не добавили, а отредактировали или удалили, фильтр по времени поступления её не увидит — у старой версии ingested_at не изменился. Такие случаи закрывает либо поле версии, либо регулярный полный пересчёт: раз в неделю или раз в месяц, по стоимости. Инкрементальность экономит ресурсы и не заменяет сверку.

with source as (
  -- учебная вставка: времени поступления строки в базе нет. Пусть событие приезжает в хранилище
  -- через час, а каждое двадцатое событие 25 августа — на трое суток позже
  select
    user_id,
    cast(event_time as date) as event_date,
    event_time + case
      when cast(event_time as date) = date '2026-08-25' and event_id % 20 = 0 then interval '3 days'
      else interval '1 hour'
    end as ingested_at
  from events
), run as (
  -- учебная вставка: прошлый запуск закончился в полночь 28 августа, окно — три дня до дня выгрузки
  select timestamp '2026-08-28 00:00:00' as watermark, date '2026-08-27' as window_start
), reload_dates as (
  select distinct s.event_date
  from source as s
  cross join run as r
  where s.ingested_at > r.watermark     -- всё, что приехало после прошлого запуска
     or s.event_date >= r.window_start  -- плюс окно с запасом
)
select
  s.event_date,
  count(*)                  as events_count,
  count(distinct s.user_id) as active_users,
  count(*) filter (where s.ingested_at > r.watermark) as new_rows
from source as s
cross join run as r
where s.event_date in (select event_date from reload_dates)
group by s.event_date
order by s.event_date;

• Храните watermark по времени поступления, а перезаписывайте по дате события.
• Ширину окна берите из обещанной задержки источника плюс день запаса.
• Замена периода должна быть идемпотентной: повторный запуск не меняет итог.
• Последние дни окна помечайте как неполные — в графике они пока не сопоставимы с остальными.
• Правки задним числом ловит периодический полный пересчёт, а не инкремент.

Мини-практика и следующий шаг

Посчитайте DAU за полный июнь, затем сгруппируйте его по неделям. Добавьте days_since_signup и проверьте D1 для пользователей из каждой когорты. В конце выпишите, какая таймзона и какое окно должны быть указаны рядом с метрикой в дашборде.