Получение списка дат в заданном диапазоне в PostgreSQL

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

Для формирования списка дат в определенном диапазоне в PostgreSQL используется функция generate_series. Например, если нужно сгенерировать даты с 1 по 10 января 2023 года, подойдет следующий запрос:

SELECT * FROM generate_series(
  '2023-01-01'::date, 
  '2023-01-10', 
  '1 day'
) AS date_list;

Результатом этого запроса будет колонка с датами, где каждая дата соответствует одному дню из заданного интервала.

Возможности функции generate_series

Функция generate_series позволяет гибко настраивать интервалы. Для генерации списка еженедельных дат выполните:

-- Готовимся к неделе новых достижений!
SELECT generate_series(
  '2023-01-01'::date, 
  '2023-01-31', 
  '1 week'::interval
) AS weekly_list;

Для генерации ежемесячных интервалов:

-- Впереди месяц новых вершин!
SELECT generate_series(
  '2023-01-01'::date, 
  '2023-12-31', 
  '1 month'::interval
) AS monthly_list;

Просто замените интервал '1 day'::interval на тот временной промежуток, который вам требуется.

В зоне от 9000

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

-- Будущее в каждом дне
CREATE TABLE date_table AS
SELECT generate_series(
   '2000-01-01'::date,
   '2050-12-31'::date,
   '1 day') AS date;

Такая таблица date_table позволяет вам легко отфильтровать, например, выходные дни:

-- Сосредотачиваемся на будних днях
SELECT date
FROM date_table
WHERE EXTRACT('dow' FROM date) NOT IN (0, 6);

Общее табличное выражение (CTE) помогает структурировать сложные запросы:

-- Порядок в датах создает порядок в данных
WITH ordered_dates AS (
  SELECT date
  FROM generate_series('2023-01-01'::date, '2023-01-31', '1 day') AS date
)
SELECT * FROM ordered_dates WHERE -- Здесь ваши условия

Когда типы данных принимают участие

Согласованность типов данных имеет ключевое значение. Работа с timestamp может выглядеть таким образом:

-- Продвигаемся с шагом в один час
SELECT generate_series(
  '2023-01-01 00:00:00'::timestamp,
  '2023-01-01 23:59:59',
  '1 hour'
) AS hourly_list;

Здесь generate_series используется с типом данных timestamp.

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

generate_series можно воспринимать как расписание поездов:

Расписание отправлений 🚉
| Дата        | Событие   |
|-------------|-----------|
| Начало      | 🚩 Отправление |
| +1 день     | 📅 Следующий |
| +2 дня      | 📅 Следующий |
| ...         | 🔄 Повторить |
| Конец       | 🏁 Финиш |

-- Все на борт временного поезда!
SELECT generate_series('2023-04-01'::date, '2023-04-10'::date, '1 day'::interval) as Date;

Представляйте generate_series как остановки между стартом и финишем вашего пути.

Особые диапазоны дат требуют особого внимания

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

-- Праздники под контролем!
SELECT date FROM generate_series(
  '2023-01-01'::date, 
  '2023-01-31', 
  '1 day'
) date
WHERE NOT EXISTS (
  SELECT 1 FROM holidays WHERE holiday_date = date
);

Здесь используется подзапрос для исключения дат, совпадающих с праздниками.

Подводные камни часовых поясов

При работе с датами и временем не забывайте учесть часовые пояса и переход на летнее время:

SET timezone = 'UTC';
SELECT generate_series(
  '2023-03-01'::timestamp with time zone,
  '2023-04-30',
  '1 day'
) AT TIME ZONE 'America/New_York' AS date_list;

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

Анализ и производительность? Впереди новые высоты!

Постоянная таблица дат может значительно ускорить анализ и производительность SQL-запросов:

-- Сочетание для всестороннего анализа
SELECT o.order_date, COUNT(o.*)
FROM orders o
INNER JOIN date_table dt ON o.order_date = dt.date
GROUP BY o.order_date;

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

1. PostgreSQL: Documentation: 16: 9.25. Set Returning Functions — официальная документация PostgreSQL, описывающая generate_series и другие функции, возвращающие наборы данных.
2. sql – generate days from date range – Stack Overflow — практические решения и дискуссии сообщества на тему генерации диапазонов дат в SQL.
3. Simplified time-series analytics: time_bucket() function — подход к анализу временных рядов в PostgreSQL с использованием функции time_bucket.
4. Range Types – PostgreSQL wiki — руководство по работе с датами и другими типами диапазонов в PostgreSQL.
5. SQL Fiddle — инструмент для тестирования и демонстрации SQL-запросов, также поддерживает PostgreSQL.
6. Category:Snippets – PostgreSQL wiki — подборка полезных фрагментов кода для работы с датами и временем в PostgreSQL.