Генерация списка дат в заданном диапазоне SQL
Быстрый ответ
Для формирования последовательности дат в указанном промежутке воспользуйтесь следующим рекурсивным CTE:
WITH RECURSIVE DateSeq AS (
SELECT CAST('2023-01-01' AS DATE) AS Date -- Стартовая дата
UNION ALL
SELECT Date + INTERVAL '1 day' FROM DateSeq WHERE Date < '2023-01-10' -- Крайняя дата
)
SELECT * FROM DateSeq; -- Показать всю последовательность
Замените '2023-01-01'
и '2023-01-10'
на нужные вам начальную и конечную даты соответственно. Если вы используете СУБД, не поддерживающую PostgreSQL, адаптируйте синтаксис под вашу систему.
Обработка больших наборов дат и применение представлений
В случаях, когда требуется обработать обширные наборы дат, и рекурсивные CTE не поддерживаются, лучше использовать функции таблицы дат или циклы в процедурном кодировании.
Действуем!
В Microsoft SQL Server можно генерировать даты при помощи функции DATEADD
, прибегнув к представлениям или таблицам чисел:
WITH Digits AS (
SELECT n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS Numbers(n)
), Seq AS (
SELECT DATEADD(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) – 1, '2023-01-01') AS Date
FROM Digits a, Digits b, Digits c
WHERE DATEADD(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) – 1, '2023-01-01') < '2023-01-10'
)
SELECT * FROM Seq;
Безграничные возможности
Если вам необходимо работать с огромными интервалами дат, используйте параметр OPTION (MAXRECURSION 0)
, чтобы SQL Server не прерывал рекурсию в процессе:
WITH RECURSIVE DateSeq AS (
-- то же самое, но в более широком масштабе...
)
SELECT * FROM DateSeq
OPTION (MAXRECURSION 0); -- Вечная рекурсия
Форматирование и сортировка результатов
Результаты должны быть представлены в приятной для глаза форме. Для этого используйте функции FORMAT()
и ORDER BY
:
-- Предыдущая CTE Seq
SELECT FORMAT(Date, 'yyyy-MM-dd') AS FormattedDate
FROM Seq
ORDER BY Date;
Визуализация
Представьте себе шкалу времени, просторяющуюся вместе с календарем:
Дата начала: 📅🔴 (1 января)
Дата окончания: 📅🔵 (7 января)
Каждый отрезок между этими датами поставлен на шкале:
📏: 🔴1️⃣➖2️⃣➖3️⃣➖4️⃣➖5️⃣➖6️⃣➖🔵7️⃣
Где каждая отметка (🔴 или 🔵) символизирует отдельный день в заданной протяженности времени.
Специфические сценарии: необычные условия
Если вам требуется сгенерировать даты, соответствующие определенным критериям (только будни или без учета праздников), применяйте условную логику:
WITH RECURSIVE DateSeq AS (
SELECT CAST('2023-01-01' AS DATE) AS Date
UNION ALL
SELECT Date + INTERVAL '1 day' FROM DateSeq
WHERE Date < '2023-01-31'
)
SELECT * FROM DateSeq
WHERE EXTRACT(DOW FROM Date) NOT IN (0, 6); -- Исключаем выходные дни
Альтернативы для других СУБД
Для PostgreSQL есть функция generate_series
:
SELECT * FROM generate_series('2023-01-01'::date, '2023-01-10'::date, '1 day');
Пользователи Oracle могут использовать подзапросы с CONNECT BY
. Дополнительные сведения смотрите в разделе Полезные материалы.
Полезные материалы
- PostgreSQL: Документация: 9.25. Функции, возвращающие наборы – документация PostgreSQL о фунциях генерации дат.
- sql – генерация дней из диапазона дат – Stack Overflow – тема на Stack Overflow c решениями для создания последовательностей дат.
- Ask TOM – советы Ask TOM по генерированию диапазона дат в базе данных Oracle.
- Типы данных даты и времени и соответствующие функции – SQL Server (Transact-SQL) – обзор фунций Microsoft SQL Server, связанных с датами.
- Функции дат | BigQuery | Google Cloud — обучение Google Cloud BigQuery о функциях генерации массивов дат.