PostgreSQL Generate Series для создания ряда дат

Вы должны быть знакомы с вводом данных в любой системе управления базами данных. При вводе данных у вас может не быть времени, и вам нужно игнорировать пробелы в данных или требовать некоторой последовательной серии записей. В этой ситуации для достижения требуемой цели можно использовать generate_series PostgreSQL. Как видно из названия, механизм этой функции содержит 2 или 3 входа. то есть, generate_series позволяет вам генерировать последовательность записей с начальной точкой, конечной точкой и увеличивающимся значением (необязательно). В основном он работает с двумя типами данных. т.е. целые числа и отметки времени. Для создания последовательности дат функция generate_series используется по-разному.

Синтаксис

>> Generate_series ([start],[stop],[{optional} step/interval]);

Описание синтаксиса запроса следующее:

• [ начало]: это отправная точка создания серии.
• [ стоп]: показывает точку, в которой серия остановится.
• [ interval]: Третье, но необязательное значение подразумевает, насколько будет увеличиваться серия на каждом шаге. Значение по умолчанию для интервалов — 1.

Давайте разберемся, как может работать функция generate_series (). Ниже приведены некоторые элементарные примеры. Чтобы понять концепцию этой функции, нам нужно установить и открыть оболочку командной строки postgreSQL (psql).

После успешной настройки и предоставления localhost, имени базы данных, номера порта и пароля мы можем выполнить любой запрос в psql.

Пример 1: Generate_series с использованием оператора DATE плюс целочисленный

Следующий запрос содержит встроенную функцию «DATE» для получения текущей даты. В то время как «а» — это предоставленный оператор. Функция этого оператора — добавить это конкретное число (интервал) в дневную часть даты. Или, другими словами, с определенными интервалами дни сдвигаются и отображаются в дате. В выходных данных интервал «9» будет добавляться каждый день, то есть 9 + 9 = 18, затем 27 и так далее, пока не будет достигнута сумма 40.

>> SELECT current_DATE + s.a AS dates FROM Generate_series(0,40,9) AS s(a);

Пример 2: Использование текущей даты для создания ряда дат

Чтобы сгенерировать ряд дат с помощью текущей даты, мы используем функцию now (), которая автоматически берет текущую дату из системы. Вы можете видеть, что соответствующий вывод показывает дату до 4 дней. Это потому, что мы ограничили выполнение, добавив 4 дня к текущей дате. Поскольку мы предоставили интервал времени до 1 дня, каждая дата будет увеличиваться на 1 добавление в день.

>> select * from generate_series(now(), now() + ‘4 days’, ‘1 day’);

Пример 3: Создание ряда дат с использованием меток времени

Отметки времени в часах : эта функция также использует тип данных отметок времени. Отметка времени — это в основном последовательность символов, которые указывают время и дату соответствующего дня. Соответствующая функция помогает пользователю указать даты между обеими датами, которые мы ожидали в запросе. Получен список меток времени от 7 до 11 с одной меткой времени каждые 5 часов.

>> select * from generate_series(‘2021-3-7 00:00’ ::timestamp,‘2021-3-11 12:00’, ‘5 hours’);

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

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

>> select * from generate_series(‘2021-03-01’::timestamptz,‘2021-03-19’::timestamptz,‘2 days’);

Пример 4: Генерация определенных дат месяца с помощью date_trunc

Первый день месяца

Если мы хотим сгенерировать первую дату текущего месяца, мы будем использовать запрос, добавленный ниже. Здесь используется особая функция date_trunc, которая усекает дату до заданной точности. Т.е. now ()

>> select date_trunc(‘month’,now());

Последний день месяца

Тот же подход date_trunc будет генерировать последний день месяца.

>> select date_trunc(‘month’,now()) + ‘1 month’::interval — ‘1 day’::interval asend_of_month;

Середина месяца

Середина месяца получается путем изменения в предыдущем запросе. Мы будем использовать функцию среднего для достижения соответствующей цели. Или мы вычтем из последнего 17 дней.

>> select date_trunc(‘month’,now()) + ‘1 month’::interval — ’17 days’::interval asmid_of_month;

Пример 5: Создание дат с использованием данных, связанных с календарем

Вот пример использования данных календаря. Мы узнаем високосный год, то есть общее количество дней в феврале месяце «. t «означает» истина «означает, что год является високосным, а для» f «- ложно,» dow «представляет дни недели. Столбец «Фев» содержит общее количество дней в месяце. «День» обозначает первый день Яна каждого года. Согласно исследованиям, недели ISO начинаются с понедельника, а первая неделя года приходится на 5 января года.

>> select date::date,extract(‘isodow’ from date) as dow,to_char(date, ‘dy’) asday,extract(‘iso year’ from date) as «iso year», extract(‘week’ from date) asweek,extract(‘day’from (date + interval ‘2 month — 1 day’))as feb,extract(‘year’ from date) as year,extract(‘day’ from (date + interval ‘2 month — 1 day’)) = 29as leap from generate_series(date ‘2010-01-01’, date ‘2020-03-01’, interval ‘1 year’) as t(date);

Isodow — это стандартный день недели по стандарту ISO. Запрос будет выполняться с 2010 по 2020 год, манипулируя каждым месяцем, неделей и днем ​​года.

Пример 6: Создание серии определенных дат и номеров дней недели

В этом запросе мы будем получать даты и номера дней, фильтруя дни недели. Будем считать дни недели численно. Например, начиная с 0 до 6. Где 0 — воскресенье, а 6 — суббота. В этом запросе вы увидите, что мы применили условие для вывода дат и номеров дней, которых нет в 2 и 5. Например, 20 февраля была суббота, поэтому появившееся число — 6.

>> with days as (select dd, extract(DOW from dd) dw from generate_series(‘2021-02-20’::date,‘2021-03-05’::date,‘1 day’::interval) dd ) select *from   days where  dw not in (2,5);

Заключение

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