How to Generate a Date Range in PostgreSQL Using generate_series?

Generating a Date Range in PostgreSQL

Determining the dates falling between a specified range is a common task in programming. In PostgreSQL, this can be achieved using the generate_series function.

With generate_series, you can generate a sequence of dates based on a starting point, an ending point, and an interval. The interval can be specified in days, months, years, or other time units.

Example:

Suppose you have the following requirements:

• Start date: June 29, 2012
• End date: July 3, 2012

To generate the list of dates within this range, you can use the following query:

select CURRENT_DATE + i 
from generate_series(date '2012-06-29'- CURRENT_DATE, 
     date '2012-07-03' - CURRENT_DATE ) i

This query generates a sequence of dates from June 29, 2012 to July 3, 2012 with an interval of one day. The result will be:

29 june 2012
30 june 2012 
1 july 2012 
2 july 2012 
3 july 2012

Alternatively, you can simplify the query using the following syntax:

select i::date from generate_series('2012-06-29', 
  '2012-07-03', '1 day'::interval) i

Both of these queries effectively generate the desired date range in PostgreSQL.

The above is the detailed content of How to Generate a Date Range in PostgreSQL Using generate_series?. For more information, please follow other related articles on the PHP Chinese website!