How to Generate a Yearly Time Series in PostgreSQL?

Generating a Yearly Time Series in PostgreSQL: A Robust Approach

Creating a time series spanning multiple years in PostgreSQL can be challenging using standard date arithmetic. A more reliable method leverages timestamps.

The Solution:

This query efficiently generates a daily time series across years:

<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 how it works:

• Timestamp Conversion: The start and end dates ('2007-02-01' and '2008-04-01') are explicitly cast to timestamps using ::timestamp. This ensures accurate handling of year boundaries.
• generate_series Function: The generate_series function creates a sequence of timestamps, incrementing by one day ('1 day'::interval).
• Date Truncation and Casting: date_trunc('day', dd) truncates each timestamp to the beginning of the day, and ::date casts the result to a date data type for cleaner output.

This approach guarantees accurate time series generation, even when dealing with dates across different years.

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