How to Efficiently Generate Time Series Between Dates in PostgreSQL?

PostgreSQL date interval time series generation method

When PostgreSQL processes time series data, it is often necessary to generate a series of dates between two given dates. A common method is to use the generate_series() function.

<code class="language-sql">select date '2004-03-07' + j - i as AllDate 
from generate_series(0, extract(doy from date '2004-03-07')::int - 1) as i,
     generate_series(0, extract(doy from date '2004-08-16')::int - 1) as j</code>

However, this approach sometimes encounters limitations when dealing with dates that span different years. To effectively resolve this situation, consider the following:

<code class="language-sql">SELECT date_trunc('day', dd):: date
FROM generate_series
        ( '2007-02-01'::timestamp 
        , '2008-04-01'::timestamp
        , '1 day'::interval) dd
        ;</code>

This query generates a range of dates between the given timestamps, converting them into a recognized date format before returning them. It maintains accuracy even when two dates span multiple years.

The above is the detailed content of How to Efficiently Generate Time Series Between Dates in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!