Создание списка дат между двумя датами в MySQL

Быстрый ответ

Для создания последовательности дат воспользуйтесь рекурсивным общим табличным выражением (CTE) в SQL. Увеличивайте каждую следующую дату на один день – от стартовой до конечной даты:

WITH Dates AS (
    SELECT CAST('2023-01-01' AS DATE) AS Date
    UNION ALL
    SELECT DATEADD(day, 1, Date)
    FROM Dates
    WHERE Date < '2023-01-10'
)
SELECT Date
FROM Dates
OPTION (MAXRECURSION 0);

Подставьте вместо '2023-01-01' и '2023-01-10' свои специфические даты, чтобы получить непрерывный список дат.

Повышение скорости обработки с помощью временных таблиц

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

CREATE TABLE #DateTable (Date DATE);

DECLARE @StartDate DATE = '2023-01-01', @EndDate DATE = '2023-01-10';

WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO #DateTable VALUES (@StartDate);
    SET @StartDate = DATEADD(day, 1, @StartDate);
END

SELECT Date FROM #DateTable;

DROP TABLE #DateTable;

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

Баланс производительности и альтернативные методы

Важно понимать баланс производительности, когда речь идет о работе с большими диапазонами дат или различными временными периодами. В SQL Server опция MAXRECURSION контролирует глубину рекурсии, в то время как в MySQL используют инкрементирование пользовательских переменных в запросе SELECT для формирования списка дат.

Подробности использования пользовательских переменных в MySQL можно найти на странице User Variables.

Работа с пустыми значениями и извлечение дат

Будьте внимательны к пустым значениям при использовании внешнего соединения (left outer join), так как они могут повлиять на целостность данных. Используйте функцию DATE для извлечения только дат из значений datetime:

SELECT COALESCE(t.Date, d.Date) as Date
FROM #DateTable d
LEFT JOIN YourDataTable t ON d.Date = DATE(t.DateTimeColumn);

Рекурсия для решения задачи в MySQL

В MariaDB, начиная с версии 10.3, и MySQL, начиная с версии 8.0, можно использовать рекурсивные общие табличные выражения для удобной генерации списка дат:

WITH RECURSIVE DateRange AS (
    SELECT '2023-01-01' AS Date
    UNION ALL
    SELECT DATE_ADD(Date, INTERVAL 1 DAY)
    FROM DateRange
    WHERE Date < '2023-01-10'
)
SELECT Date
FROM DateRange;

Для безопасности в MySQL установите разделители перед блоками кода процедур.

Визуализация

Восприятие процесса можно представить как последовательное пролистывание страниц календаря с начала до конца:

Стартовая дата (🗓️ Начало): [1 мая]
Конечная дата (🗓️ Конец): [5 мая]

На каждом шаге генерируется новая дата, отдаляющая вас от начала и приближающая к концу:

🗓️ Начало ➡️ [2 мая] ➡️ [3 мая] ➡️ [4 мая] ➡️ 🗓️ Конец

Это можно сравнить с путешествием по времени, где каждый новый день – это новый лист в календаре.

Путь к мастерству: комплексные решения

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

Эффективное использование временных таблиц

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

Будьте в тренде обновлений SQL

Оставайтесь в курсе последних нововведений в SQL и следите за обновлениями стандартов. Никогда не прекращайте учиться и совершенствовать свои профессиональные навыки.

Полезные материалы

1. PostgreSQL: Documentation: 9.25. Set Returning Functions — подробный обзор функции generate_series в PostgreSQL.
2. Date and Time Data Types and Functions – SQL Server (Transact-SQL) — описание функций для работы с датами и временем в SQL Server.
3. A Closer Look at CXPACKET Wait Type in SQL Server — анализ видов ожидания запросов в SQL Server, актуальный для работы с большими массивами дат.