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

Коротко

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

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

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

Для июня не нужно вычислять “последнюю секунду месяца”. Следующий месяц уже является естественной правой границей. Это работает одинаково для DATE и TIMESTAMP и не создаёт проблем с миллисекундами.

select\n  count(*) as events_count,\n  count(distinct user_id) as active_users\nfrom events\nwhere event_date >= date '2026-06-01'\n  and event_date < date '2026-07-01';

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

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

select\n  date_trunc('week', event_date)::date as week_start,\n  count(distinct user_id) as active_users\nfrom events\nwhere event_name = 'core_action'\n  and event_date >= date '2026-06-01'\n  and event_date < date '2026-07-01'\ngroup by week_start\norder by week_start;

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

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

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

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

select\n  u.user_id,\n  u.signup_date,\n  e.event_date,\n  e.event_date - u.signup_date as days_since_signup\nfrom users as u\njoin events as e using (user_id)\nwhere e.event_name = 'core_action'\n  and e.event_date >= u.signup_date\norder by u.user_id, e.event_date;

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

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

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

select\n  (occurred_at at time zone 'Europe/Moscow')::date as business_day,\n  count(distinct user_id) as active_users\nfrom events_with_timestamp\nwhere occurred_at >= timestamp '2026-06-01 00:00:00+03'\n  and occurred_at < timestamp '2026-07-01 00:00:00+03'\ngroup by business_day\norder by business_day;

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

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

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

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

Если timestamp хранится в UTC, сначала переведи его в часовую зону отчёта, а уже потом выделяй дату. Иначе событие в 23:30 UTC может оказаться на соседнем календарном дне для пользователя. Для глобального продукта это меняет DAU, revenue и распределение активности по часам.

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

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

select
  (occurred_at at time zone 'UTC'
    at time zone 'Europe/Moscow')::date as business_date,
  count(distinct user_id) as dau
from events
where occurred_at >= timestamp '2026-06-30 21:00:00'
  and occurred_at < timestamp '2026-07-31 21:00:00'
group by business_date
order by business_date;

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

В отчёте нужно различать отсутствие событий и отсутствие строки. Если 5 июля в продукте действительно никто не совершил core action, это ноль. Если загрузка за 5 июля ещё не приехала, это NULL или статус incomplete. Создай календарную сетку и присоедини агрегат к ней, чтобы пропущенные даты были видны явно.

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

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

with calendar as (
  select generate_series(
    date '2026-07-01',
    date '2026-07-31',
    interval '1 day'
  )::date as day
), daily as (
  select event_date, count(distinct user_id) as dau
  from events
  group by event_date
)
select
  c.day,
  d.dau,
  case when d.event_date is null then 'no_row' else 'observed' end as state
from calendar as c
left join daily as d on d.event_date = c.day
order by c.day;

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

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