Postgresql Generate_Series to Create a Date Series

You must be familiar with data entry in any database management system. While entering data, you may don’t have time, and you need to ignore gaps in your data or want some consistent series of records. In this situation, PostgreSQL generate_series is applicable to achieve the required goal. As the name indicates, the mechanism of this function contains either 2 or 3 inputs. i.e., generate_series allows you to generate a sequence of records with a starting point, terminating point, and incrementing value (optional). It mainly works on two data types. i.e., Integers and timestamps. To create a sequence of dates, the generate_series function is utilized in different ways.

Syntax:

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

The description for query syntax is as follow:

[start]: It is the starting point of generating a series.
[stop] : This shows the point where the series will stop.
[interval]: The third but optional value implies how much the series will increment in each step. The default value for intervals is 1.

Let’s have an idea of how the generate_series() function can perform. Below are some elementary examples. To understand this function’s concept, we need to install and open the postgreSQL command-line shell (psql).

After successful configuration and while providing localhost, database name, port number, and password, we can go through any query on psql.

Example 01: Generate_series using DATE plus integer operator

The following query contains a “DATE” built-in function to fetch the current date. Whereas “a” is the operator provided. This operator’s function is to add that particular number(interval) in the day’s portion of the date. Or in other words, with specific intervals, days are shifted and shown in the date. In the output, the “9” interval will be added each day, i.e., 9+9=18, then 27, and so on, till the sum of 40 is attained.

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

Example 02: Using current date to generate date series

To generate date series with the current date’s help, we are utilizing the now() function, which takes the current date automatically from the system. You can see the corresponding output shows the date up to 4 days. This is because we have limited the execution by adding 4 days to the current date. As we have provided the interval time to 1 day, so each date will be incremented with 1 addition in day

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

Example 03: Generating date series using Timestamps

Timestamps of hours: This function also uses the datatype of timestamps. The timestamp is basically a sequence of characters that provide the time and date of a related day. The corresponding function facilitates the user in providing dates between both dates we used to anticipate in the query. The list of timestamps from date 7 to 11 with one timestamp every 5 hours is obtained.

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

The query, as mentioned above, is also used to add minutes and seconds with hours to acquire a better timestamp between days of relevant timestamp.

Timestamps of days: In the exceeding example, we have seen that timestamp is used to show dates between the two respective dates we have provided with the alteration in hours incremented by 5. In the current example, we will see the timestamp in days. Days are incremented with 2 as we have initiated a 2- day gap in the particular output.

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

Example 04: Generating specific dates of the month using date_trunc

First day of the month

If we want to generate the first date of current month we will use the query appended below.The distinct function used here is date_trunc ,which truncates the date to given precision.i.e. now()

>> select date_trunc('month',now());

Last day of the month

The same date_trunc approach will generate the Last day of the month.

>> select date_trunc('month',now()) + '1 month'::interval - '1 day'::interval as end_of_month;

Mid of the month

Mid of the month is obtained by altering in the previous query. We will utilize the mean function to obtain the respective goal. Or we will subtract 17 days from the last one.

>> select date_trunc('month',now()) + '1 month'::interval - '17 days'::interval as mid_of_month;

Example 05: Generating dates using calendar-related data

Here comes the example of using calendar data. We will come to know the leap year, i.e., total days in February month.” t” denotes true means year is a leap year, and for “f,” it is false “dow” represents the days of the week. The “Feb” column contains total days in the month. “day” denotes Jan’s first day of each year. According to research, weeks of ISO start from Monday, and the first week of a year contains January 5 of the year.

>> select date::date,extract('isodow' from date) as dow,to_char(date, 'dy') as day,extract('iso year' from date) as "iso year", extract('week' from date) as week,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 is the “ISO” standard day of the week. The query will execute from 2010 to 2020 while manipulating each month, week, and day of the year.

Example 06: Generating series of specific dates and days number in the week

In this query, we will acquire dates and day numbers by filtering days in a week. We will numerically consider days of the week. For instance, starting from 0 to 6. Where 0 is Sunday and 6 is Saturday. In this query, you will see that we have applied a condition to bring dates and day numbers that are not in 2 and 5. For example, on February 20, it was Saturday, so the number that appeared is 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);

Conclusion

The article, as mentioned above, covers the majority of basic functionalities related to generating series to create date series. Detailed examples discussed in every aspect are so meaningful that they will escalate your respective article’s knowledge.