-1

If I run the following query I get really strange results. I get week 1 (as expected) only starting from Monday 4th of January. I using this in a group by query to count covid cases per week and this totaly ruins my day :/

SELECT date_part('week', '2021-01-01'::date) as week, date_part('year', '2021-01-01'::date) as year;
week year
53   2021

Version: PostgreSQL 12.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.3.0) 9.3.0, 64-bit

Daniel Gerber
  • 3,226
  • 3
  • 25
  • 32

1 Answers1

2

As documented in the manual 'week' uses the ISO definition for the first week.

To get the corresponding (ISO) year, use 'isoyear'

SELECT date_part('week', '2021-01-01'::date) as week, 
       date_part('isoyear', '2021-01-01'::date) as year;

The above correctly returns 53, 2020

  • Thank you. It never occured to me (and it still doesnt make sense to me) that the week number „reach into” the new year. – Daniel Gerber Jan 13 '21 at 16:21