Даты и время: вопросы для собеседования (часть 4)
Работа с датами в SQL — это DATE_TRUNC, EXTRACT, интервалы, приведение типов и группировка по временным периодам. На собеседованиях часто просят посчитать метрику по неделям или месяцам, найти разницу между датами, или отфильтровать данные за скользящее окно. Ошибки в работе с таймзонами и форматами дат — частая причина провала на интервью.
Вопросы 16–20 из 20
16Какое описание лучше всего соответствует типу `timestamptz` в PostgreSQL?
AЭто строковый тип, в котором всегда хранится исходная таймзона пользователя без приведения к UTC
BЭто тип, который хранит абсолютный момент времени, а при выводе учитывает таймзону сессии или указанную явно
CЭто тот же `date`, но с более точным форматом хранения значений, не различающим часовые пояса
DЭто тип для дат и времени, который нельзя использовать в сравнениях, сортировке и индексах из-за таймзон
Ответ: `timestamptz` описывает момент времени; отображение и приведение зависят от таймзоны сессии или указанной явно.
`timestamptz` хранит абсолютный момент времени, нормализуя его к UTC. При отображении и некоторых преобразованиях используется таймзона сессии или явно заданная в выражении. В отличие от `timestamp` без таймзоны, `timestamptz` помогает корректно работать с событиями из разных регионов и не ломается при сравнениях и сортировке. Это не строка и не более точная версия `date`.
Подробный разбор → 17Нужно отобрать события ровно за вчерашний календарный день по `created_at` типа `timestamp`, чтобы не задвоить границы при ежедневном запуске. Какое условие в `WHERE` лучше?
A`WHERE created_at >= current_timestamp - INTERVAL '24 hours' AND created_at < current_timestamp`: окно от текущего момента
B`WHERE created_at > current_date - INTERVAL '1 day' AND created_at <= current_date`: обе границы как замкнутый интервал
C`WHERE created_at BETWEEN current_date - INTERVAL '1 day' AND current_date`: оператор `BETWEEN` включает обе границы интервала
D`WHERE created_at >= current_date - INTERVAL '1 day' AND created_at < current_date`: полуинтервал на базе календарного дня
Ответ: Для календарного дня используйте полуинтервал `[start, end)` на базе `current_date`.
Удобнее задавать границы как `created_at >= start AND created_at < end`. Тогда событие ровно на границе (например, в `00:00:00`) попадёт только в один день и не будет задваиваться, в отличие от `BETWEEN`, который включает обе границы. Сравнение с `current_timestamp` даёт скользящие 24 часа, а не календарный день.
Подробный разбор → 18Колонка `created_at` имеет тип `timestamptz`. Как получить дату события в UTC (тип `date`), чтобы результат не зависел от таймзоны сессии?
A`created_at::date`: приведение к `date` без явной таймзоны опирается на текущие настройки сессии
B`(created_at AT TIME ZONE 'UTC')::date`: явный перевод момента в UTC и затем отбрасывание времени до даты
C`DATE_TRUNC('day', created_at)::date`: округление по таймзоне сессии и приведение `timestamptz` к типу `date`
D`(created_at AT TIME ZONE 'Europe/Moscow')::date`: перевод момента в московское время с приведением к `date`
Ответ: Приведение `timestamptz` к `date` зависит от таймзоны сессии; для UTC сначала используйте `AT TIME ZONE 'UTC'`.
Когда вы делаете `created_at::date`, PostgreSQL сначала переводит `timestamptz` в локальное время таймзоны сессии, затем отбрасывает время. Чтобы получить UTC-дату, явно переведите момент времени в UTC: `(created_at AT TIME ZONE 'UTC')::date`. Вариант с `Europe/Moscow` даст московскую дату, а `DATE_TRUNC` без явной таймзоны имеет ту же проблему, что и обычный каст.
Подробный разбор → 19Колонка `created_at` имеет тип `timestamptz`. Таймзона сессии может отличаться между задачами. Как гарантированно сгруппировать события по UTC-дням?
A`DATE_TRUNC('day', created_at)`: результат опирается на таймзону текущей сессии и в общем случае не даёт стабильных UTC-дней
B`DATE_TRUNC('day', created_at::timestamp)`: приведение к `timestamp` молча использует таймзону сессии при отбрасывании пояса
C`DATE_TRUNC('day', created_at AT TIME ZONE 'Europe/Moscow')`: бакеты строятся по московским календарным дням
D`DATE_TRUNC('day', created_at AT TIME ZONE 'UTC')`: явный перевод момента в UTC даёт стабильные дни независимо от сессии
Ответ: Для стабильных UTC-бакетов на `timestamptz` явно переводите время в UTC перед `DATE_TRUNC`.
Операции над `timestamptz` зависят от текущей таймзоны сессии: `DATE_TRUNC` без явного приведения может в разных запусках попадать на разные сутки. Если отчёт должен считать дни строго по UTC, сначала приведите момент к UTC через `created_at AT TIME ZONE 'UTC'`, а затем применяйте `DATE_TRUNC('day', ...)`. Приведение к `timestamp` без таймзоны не решает задачу: оно опирается на ту же сессионную таймзону. Перевод в `Europe/Moscow` даст бакеты по московским дням, а не по UTC.
Подробный разбор → 20В таблице `events(user_id, created_at)` колонка `created_at` типа `timestamptz` (события записаны в UTC). Нужно посчитать DAU по дням в часовом поясе Москвы и не зависеть от часового пояса сессии. Какой бакет правильный?
A`DATE_TRUNC('day', created_at AT TIME ZONE 'Europe/Moscow')`: перевод в локальное время Москвы
B`DATE_TRUNC('day', created_at::timestamp)`: приведение типа без указания часового пояса
C`DATE_TRUNC('day', created_at)`: без явного перевода в часовой пояс Москвы
D`DATE_TRUNC('day', created_at AT TIME ZONE 'UTC')`: повторный перевод в исходный `UTC`
Ответ: Для локальных дней сначала переведите метку времени в нужный часовой пояс, затем применяйте `DATE_TRUNC`.
Для группировки `timestamptz` по дням в Москве сначала приводят значение к локальному времени через `AT TIME ZONE 'Europe/Moscow'`, а потом бакетируют через `DATE_TRUNC('day', ...)`. Иначе результат зависит от часового пояса сессии или остаётся в UTC, и часть событий «уезжает» в соседний день. Простое приведение типа `::timestamp` теряет информацию о часовом поясе и тоже даёт смещение.
Подробный разбор →