How to Generate a Time Series Between Non-Consecutive Years in PostgreSQL?

Generating a Time Series Across Non-Consecutive Years in PostgreSQL

Generating a complete time series between two dates in PostgreSQL requires careful consideration, especially when dealing with dates spanning multiple years. Common methods using generate_series with extract(doy) can produce inaccurate results in such scenarios.

A More Robust Approach

A superior solution leverages PostgreSQL's generate_series function in conjunction with date_trunc and interval. This technique reliably generates time series across any date range, regardless of the year.

The following query exemplifies this improved method:

<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>

Here's a breakdown:

• generate_series('2007-02-01'::timestamp, '2008-04-01'::timestamp, '1 day'::interval): This generates a sequence of timestamps, incrementing by one day, between the specified start and end dates.
• date_trunc('day', dd): This function truncates the timestamps to the start of each day, removing the time component.
• ::date: This casts the resulting timestamps to the date data type for cleaner output.

This refined approach offers a precise and dependable method for creating time series across non-consecutive years within PostgreSQL.

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