How to Generate a Date List within a Specific Range in PostgreSQL?

Getting a Date List within a Range in PostgreSQL

In PostgreSQL, retrieving a list of days between two given dates can be easily achieved using a combination of the CURRENT_DATE function and the generate_series() function. This method provides flexibility in defining the range and the interval between dates.

The CURRENT_DATE function returns the current system date, while the generate_series() function generates a sequence of dates based on the provided parameters.

Example:

Consider the following scenario:

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

To retrieve the list of days between these two dates, 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

The output will be:

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

Alternatively, a more concise query can be used:

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

This query explicitly casts the i value to a date data type, ensuring that the output contains only dates.

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