Функции даты SQL: как группировать по месяцам, не теряя ни одного

1. Дата в SQL — это реальный тип

Перед пояснением: в Excel дата — это число в определенном формате. Скажите, что, по вашему мнению, находится в базе данных.

Свой тип, со своими правилами.

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'order_date';

order_date | DATE

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

Выделяются три типа, и стоит уметь их назвать. DATE это календарный день без времени. TIMESTAMP это день и время. TIMESTAMP WITH TIME ZONE это день, время и положение на земном шаре, и именно он превращает отчет за понедельник в отчет за воскресенье для кого-то в другом офисе. Если ваш исходный столбец представляет собой метку времени, а ваш вопрос касается дней, задайте его заранее и скажите об этом.

2. DATE_TRUNC и группировка по месяцам

DATE_TRUNC берет единицу измерения и дату и выбрасывает все, что меньше этой единицы. Усечение месяца превращает 19 января в 1 января, что делает все даты января идентичными, что позволяет их группировать.

SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(units * unit_price)          AS revenue,
       COUNT(*)                         AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

2026-01-01 | 2630 | 4
2026-02-01 | 2245 | 4
2026-03-01 | 2585 | 4
2026-05-01 | 2430 | 4

Четыре строки, и доход составит 9890, что соответствует общему значению таблицы, поэтому ни одна строка не ускользнет от группировки. Прежде всего стоит обратить внимание на две вещи, касающиеся вывода.

Месяц - это Дата, а не этикетка. Вот и вся причина предпочесть DATE_TRUNC в строку: результат по-прежнему сортируется в хронологическом порядке, его можно сравнивать и присоединить к календарю. Некоторые движки, в том числе DuckDB, возвращают временную метку из DATE_TRUNC и отобразить его с нулевым временем в конце; отбросить его обратно с ::DATE если вас беспокоят конечные нули.

И есть четыре ряда за период, который длится с января по май. Удерживайте эту мысль до шестого раздела.

Строковая альтернатива работает и отказывается от типа:

SELECT strftime(order_date, '%Y-%m') AS month, SUM(units * unit_price)
FROM orders GROUP BY 1 ORDER BY 1;

2026-01 | 2630
2026-02 | 2245
2026-03 | 2585
2026-05 | 2430

Те же цифры и '2026-01' теперь текст. Сортировка происходит правильно, потому что в формате первым ставится год, что является скорее удачей, чем дизайном, и его больше нельзя использовать в арифметике дат. Используйте его для отображения в самом конце, а не для группировки.

Все о зерне от GROUP BY и HAVING здесь применяется без изменений: после этого запроса одна строка равна одному месяцу, а в результате отдельный заказ больше не существует.

3. Разбираем детали

EXTRACT извлекает компонент как число, и большинство движков добавляют рядом с ним более дружелюбные помощники.

SELECT order_date,
       EXTRACT(YEAR  FROM order_date) AS y,
       EXTRACT(MONTH FROM order_date) AS m,
       EXTRACT(DAY   FROM order_date) AS d,
       DAYNAME(order_date)            AS dow
FROM orders LIMIT 1;

2026-01-05 | 2026 | 1 | 5 | Monday

Одно предупреждение, которое стоит людям целого отчета. EXTRACT(MONTH FROM ...) возвращает число от 1 до 12 без указания года, поэтому группировка по нему объединяет январь 2025 года с январем 2026 года в одну строку с пометкой 1. Это хорошо для вопроса сезонности и неправильно для тренда, и на странице они выглядят одинаково. Если вы группируете по номеру месяца, группируете также по году или используете DATE_TRUNC и избежать выбора.

4. Фильтрация периода полуоткрытым способом

Перед объяснением: вы хотите март. Скажите, какие из них вы бы написали и отличаются ли они.

WHERE order_date BETWEEN '2026-03-01' AND '2026-03-31'
WHERE order_date >= DATE '2026-03-01' AND order_date < DATE '2026-04-01'

На чистом DATE столбец они соглашаются, и второй вернул 2585 за март в пробеге. На TIMESTAMP столбец они совершенно не согласны, потому что заказ в 09:14 31 марта - это позже, чем в полночь 31 марта, поэтому BETWEEN исключает это. Целый день активности исчезает, и только в те месяцы, последний день которых оказывается занятым.

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

Постройте границу как реальный литерал даты, DATE '2026-03-01', а не пустую строку, поэтому движок сравнивает даты с датами, а не угадывает.

5. Арифметика дат, и где два движка расходятся во мнениях

Вычитание двух дат дает дни в виде числа:

SELECT DATE '2026-05-25' - DATE '2026-01-05';    140

Добавление периода использует INTERVAL, и вот тут становится интересно. Задайте два разных двигателя на один месяц после 31 января:

DuckDB   SELECT DATE '2026-01-31' + INTERVAL 1 MONTH;      2026-02-28
SQLite   SELECT date('2026-01-31', '+1 month');            2026-03-03

Оба варианта оправданы, и это не один и тот же ответ. DuckDB фиксирует последний действительный день целевого месяца. SQLite добавляет единицу к номеру месяца, а затем нормализует 31 февраля вперед на март. В феврале 2026 года 28 дней, поэтому 31 февраля становится 3 марта.

Скажите вслух, что это делает с отчетом, который добавляет один месяц к каждому началу контракта, чтобы получить дату продления. Тридцать дней в месяце ничего не происходит. Тридцать первого числа один двигатель продлевается в конце февраля, а другой — в марте, и если в двух системах реализовать одну и ту же логику, то они разойдутся во мнениях именно по этим контрактам.

Урок не в том, что один двигатель неисправен. Дело в том, что арифметика месяца в конце месяца представляет собой настоящую двусмысленность, поэтому, если это имеет значение для ваших данных, проверьте ее 31-го числа на движке, который вы действительно используете, а не предполагаете. Это занимает один запрос.

Еще два, которые появляются постоянно:

SELECT DATE_TRUNC('month', DATE '2026-01-19');    2026-01-01    first of the month
SELECT LAST_DAY(DATE '2026-01-19');               2026-01-31    last of the month

6. Месяц, который никогда не становится ссорой

Перед пояснением: данные обрабатываются с 5 января по 25 мая, а ежемесячный запрос вернул четыре строки. Скажите, какой месяц пропущен и почему.

Апрель, потому что апрельских заказов нет. GROUP BY может создать строку только для значения, которое существует в данных, поэтому месяц, в котором ничего нет, ничего не дает. Запрос на это прямо подтверждает, что на самом деле там ничего нет:

SELECT COUNT(*) AS rows_in_april, SUM(units * unit_price) AS april
FROM orders
WHERE order_date >= DATE '2026-04-01' AND order_date < DATE '2026-05-01';

0 | NULL

Обратите внимание на NULL. SUM отсутствие строк не равно нулю, это неизвестно, что является правильным ответом, и это еще одна вещь, с которой нужно справиться, а не раздражаться.

Теперь посмотрите, как этот разрыв влияет на расчет роста. Вот месяц за месяцем с LAG, запустите четыре строки полученных данных:

2026-01-01 | 2630 |   NULL
2026-02-01 | 2245 |  -14.6
2026-03-01 | 2585 |   15.1
2026-05-01 | 2430 |   -6.0

LAG означает «предыдущая строка», а предыдущая строка для мая — март. -6,0 — это реальное сравнение двух реальных месяцев с интервалом в два месяца, представленное в столбце «Месяц за месяцем».

Исправление — календарь: генерируйте каждый месяц в периоде, затем слева присоединяйте к нему данные, так что месяц без заказов отображается в виде строки с нулем.

WITH months AS (
  SELECT UNNEST(generate_series(DATE '2026-01-01', DATE '2026-05-01',
                                INTERVAL 1 MONTH)) AS month
),
by_month AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(units * unit_price)          AS revenue
  FROM orders GROUP BY 1
),
filled AS (
  SELECT m.month, COALESCE(b.revenue, 0) AS revenue
  FROM months m
  LEFT JOIN by_month b ON b.month = m.month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev,
       ROUND(100.0 * revenue
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) - 100, 1) AS pct
FROM filled
ORDER BY month;

2026-01-01 | 2630 | NULL |   NULL
2026-02-01 | 2245 | 2630 |  -14.6
2026-03-01 | 2585 | 2245 |   15.1
2026-04-01 |    0 | 2585 | -100.0
2026-05-01 | 2430 |    0 |   NULL

Теперь пять рядов, и три цифры изменили значение. Апрель — это видимый ноль и истинное значение −100,0. Процент Мэй составляет NULL а не -6,0, потому что NULLIF превратил нулевой знаменатель в ноль, и дивизия отказалась отвечать, что совершенно верно: рост из ничего не имеет процента.

Стоит назвать три части этого запроса, поскольку они представляют собой скорее закономерность, чем конкретику. generate_series делает календарь; эквивалент в Постгрес — это та же функция без UNNEST, а в движках без него держишь небольшую таблицу дат. LEFT JOIN от календаря к данным, а не наоборот, поэтому календарь решает, какие строки существуют. И COALESCE(..., 0) превращает полученный нуль в ноль, который вы на самом деле имеете в виду, намеренно, в одном месте.

Та же арифметика и та же ловушка в электронной таблице версия этой страницы в Excelи LAG механика сама по себе находится в ежемесячно с LAG.

7. SQLite не имеет типа даты

Это удивляет людей, изучавших SQL на SQLite, то есть большинства новичков, поэтому об этом стоит сказать прямо. SQLite принимает DATE в определении столбца, а затем сохраняет все, что вы ему дадите. Спрашиваем, что там на самом деле содержится:

SELECT typeof(order_date), order_date FROM orders LIMIT 1;

text | 2026-01-05

Текст. Столбец был объявлен как DATE и значение является строкой. Все по-прежнему работает, пока строки записаны YYYY-MM-DD, поскольку этот формат правильно сортируется и сравнивается с текстом. Выйдите за его пределы, и функции не будут жаловаться, они ничего не возвращают:

SELECT strftime('%Y-%m', '05/01/2026');    NULL

Нуль, а не ошибка, которая перейдет в GROUP BY как нулевую группу и сидеть там, выглядя скорее как проблема с данными, чем как проблема с форматом.

Остальная часть обработки даты в SQLite следует из отсутствия типа. Группировка потребностей strftime, нужны различия julianday, и оба корректно работали с одними и теми же данными:

SELECT strftime('%Y-%m', order_date), SUM(units*unit_price)
FROM orders GROUP BY 1 ORDER BY 1;
2026-01 | 2630   ...   2026-05 | 2430

SELECT julianday('2026-05-25') - julianday('2026-01-05');    140.0

Итак: в SQLite сохраняйте даты как YYYY-MM-DD строки и ничего больше, и проверяйте формат при импорте, а не доверяйте ему. В типизированном движке пусть столбец будет датой, и пусть движок применяет ее. И когда запрос ведет себя странно на одном движке и правильно на другом, система типов — это первое место, на которое следует обратить внимание, а не последнее.

8. Сортировка и ловушка названия месяца

Перед пояснением: форматируете месяц как короткое название отчета, далее упорядочиваете по нему. Прогнозируйте порядок.

SELECT strftime(order_date, '%b') AS month, SUM(units*unit_price)
FROM orders GROUP BY 1 ORDER BY 1;

Feb | 2245
Jan | 2630
Mar | 2585
May | 2430

Первое февраля. Столбец текстовый, поэтому сортировка ведется по алфавиту, а в алфавите февраль превосходит январь. Любой, кто прочитает построенный на его основе график, увидит тенденцию, которой никогда не было.

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

SELECT strftime(m.month, '%b')  AS label,
       m.revenue
FROM ( SELECT DATE_TRUNC('month', order_date) AS month,
              SUM(units*unit_price)            AS revenue
       FROM orders GROUP BY 1 ) m
ORDER BY m.month;

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

Пограничные случаи, которые нарушают запрос даты

Шесть, которые пройдут.

Временные метки по сравнению с датами. Столбец отметки времени по сравнению с DATE '2026-03-31' означает полночь, поэтому все, что происходит позже в этот день, исключается. Полуоткрытые диапазоны делают это невозможным.

Часовые пояса. Временная метка, хранящаяся в формате UTC и прочитанная кем-то в другой зоне, может перемещать строки между днями, что перемещает их между месяцами в границах месяцев. Решите, в какой зоне находится отчет, и выполните одно явное преобразование.

Функция для отфильтрованного столбца. WHERE EXTRACT(YEAR FROM order_date) = 2026 останавливает большинство механизмов, использующих индекс по этому столбцу, а в большой таблице превращает быстрый запрос в медленный. Вместо этого сравните необработанный столбец с диапазоном.

Номер месяца без года. Это описано в третьем разделе, и его стоит повторить, поскольку он дает двенадцать аккуратных строк из данных за три года.

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

Недели. DATE_TRUNC('week', ...) должен решить, начинается ли неделя в воскресенье или в понедельник, а двигатели различаются. Проверьте это один раз на известную дату, а не предполагайте.