Question 402568 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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:

    Login or Signup to reply.
  2. 0

    I’m not sure exactly why you’re doing it this way, but what you can do is the following – if your second date isn’t less than 1 year from your first, then you can use the following (hacky!) code – if you’re taking the dates from somewhere else.

    (all of the code below is available on the fiddle here):

    This makes use of the PostgreSQL AGE() function:

    age ( timestamp, timestamp ) → interval

    Subtract arguments, producing a “symbolic” result that uses years and
    months, rather than just days

    age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’) → 43 years 9 mons
    27 days

    age ( timestamp ) → interval

    Subtract argument from current_date (at midnight)

    age(timestamp ‘1957-06-13’) → 62 years 6 mons 10 days

    SELECT 
  EXTRACT('YEAR' FROM gen_year.n) AS the_year  -- as specific as possible!
  FROM GENERATE_SERIES
  (
    '2022-06-14 11:00:00.000+00'::timestamptz, 
  
    CASE
      WHEN
        AGE
        (
          '2023-06-13 11:00:00.000+00'::timestamptz, 
          '2022-06-14 11:00:00.000+00'::timestamptz
        )
        < '1 YEAR'::INTERVAL 
        THEN '2022-06-14 11:00:00.000+00'::timestamptz
        + '1 YEAR'::INTERVAL
      ELSE
        '2023-06-13 11:00:00.000+00'::timestamptz
    END,
  '1 YEAR'::INTERVAL
) AS gen_year(n)

--
-- returns INTEGER values rather than strings for a number - **_always_** better!
--

    Result:

    the_year
2022
2023
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search