Postgres – Get ISO Year Start & ISO Year End

Today I had to get the ISO year start and the ISO year end for a given date. For instance, according to ISO8601 the week number for January 30, 2020 is 5:

SELECT extract(week FROM '2020-01-30'::DATE);

Year Limits By Date In Year

Let’s say that we want to determine the first date of the year and the last date of the year. The following gives us just that for today’s date:

SELECT
  d.this_year - (extract(week FROM d.this_year) * 7 - 8 + extract(isodow FROM d.this_year))::INT AS year_start,
  d.next_year - (extract(week FROM d.next_year) * 7 - 7 + extract(isodow FROM d.next_year))::INT AS year_end
FROM (
  SELECT
    (d.y || '-01-04')::DATE AS this_year,
    (d.y + 1 || '-01-04')::DATE AS next_year
  FROM (SELECT extract(isoyear FROM CURRENT_DATE)::INT AS y) d
) d;

Year Limits By Year Number

Now let’s say that we want to determine the first date of the year and the last date of the year from the number of the year. The following gives us just that for the year 2025:

SELECT
  d.this_year - (extract(week FROM d.this_year) * 7 - 8 + extract(isodow FROM d.this_year))::INT AS year_start,
  d.next_year - (extract(week FROM d.next_year) * 7 - 7 + extract(isodow FROM d.next_year))::INT AS year_end
FROM (
  SELECT
    (d.y || '-01-04')::DATE AS this_year,
    (d.y + 1 || '-01-04')::DATE AS next_year
  FROM (SELECT 2025 AS y) d
) d;

All Weeks In 2020

Here is a list of the 53 weeks for the year 2020:

Logic Behind ISO8601

When using extract(week FROM …) PostgreSQL always gets the ISO week. You can use extract(isodow FROM …) to get the ISO day of the week (1-7). You can use extract(isoyear FROM …) to get the ISO year.

Using this logic, the first week of the year has January 4th in it. The last week in the year contains December 28th in it. In other words the ISO year ranges from the week of January 4th to the week of December 28th. In essence the first week of the year must have the majority of the days of the first week (Sunday to Saturday) in it and the same goes for the last week of the year.

Hopefully the logic behind of this and maybe even a portion of the queries will help you too. Happy coding!!! 😎