How to Generate a Date Range in PostgreSQL?

Generating a Date Range with PostgreSQL

Problem:

You need to retrieve a list of dates between two specified dates, inclusive, from a PostgreSQL database. For instance, given the start date June 29, 2012, and the end date July 3, 2012, the desired output would be:

29 June 2012
30 June 2012 
1 July 2012 
2 July 2012 
3 July 2012

Solution:

In PostgreSQL, you can efficiently generate a sequence of dates within a range using the generate_series() function. The following query will provide the desired output:

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

An alternative, more concise syntax is:

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

Both queries will generate the date range specified, and you can adjust the interval parameter as needed to generate ranges based on different time units, such as hours, weeks, or months.

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