sqlpostgresqltimestampgenerate-seriespostgres-14

Generate all years between two timestamps

As part of a larger Postgres query, I generate all years between two given timestamps (timestamptz):

select to_char(generate_series, 'YYYY') from generate_series(
'2022-06-14 11:00:00.000+00'::timestamptz, 
'2023-06-14 11:00:00.000+00'::timestamptz,
'1 year' );

Which returns:

'2022'
'2023'

The issue is, if there is less than one year between both timestamps, only the first year is returned. I need a set of all involved years, regardless of the the interval between both timestamps, e.g.:

select to_char(generate_series, 'YYYY') from generate_series(
'2022-06-14 11:00:00.000+00'::timestamptz, 
'2023-06-13 11:00:00.000+00'::timestamptz,
'1 year' );

Only returns:

'2022'

But I would like it to return:

'2022'
'2023'

Is there some way to achieve this?

Solution

  • The year of a timestamptz (timestamp with time zone]) value is not strictly determined. It's still the year 2024 in New York, when I wish "Happy New Year 2025" in Vienna.
    Only date or timestamp (timestamp without time zone) are deterministic in this regard.

    To avoid corner case issues you must define the time zone for your query in one way or another.

    Here is one way:

    SELECT generate_series(EXTRACT('year' FROM '2022-06-14 11:00+0'::timestamptz AT TIME ZONE 'Europe/Vienna')::int
                     , EXTRACT('year' FROM '2023-06-13 11:00+0'::timestamptz AT TIME ZONE 'Europe/Vienna')::int) AS the_year;

    Here is another:

    SELECT EXTRACT('year' FROM ts)::int AS the_year
FROM   generate_series(date_trunc('year', '2022-06-14 11:00+0'::timestamptz, 'Europe/Vienna')
                     , date_trunc('year', '2023-06-13 11:00+0'::timestamptz, 'Europe/Vienna')
                     , interval '1 year') ts;

    date_trunc() allows a 3rd parameter to pass the time zone since Postgres 12 - which only makes sense for timestamptz, obviously.

    Replace with your time zone, or use timestamp values to begin with.

    Assuming the second timestamp is guaranteed to be after the first, or you have to do more / define how to deal with it.

    Basics: