3

I've been banging my head towards a solution to retrieving a consistent (year, week) pair in PostrgeSQL.

After looking at the question here, and, in particular, at this comment:

Grouping by date_part('year', x), date_part('week', x) is most likely not what the user wants and should be considered incorrect. For example the first day of 2017 is still part of week 52 that started in 2016 and therefore the (2017, 52) pair is probably not at all what you want as it sorts to the end of 2017. Using ´date_trunc('week', x)` on the other hand works as one would expect.

I tried some combination of date_trunc and date_part. However, none seem to give consistent results.

For instance: consider these two dates between different years: '2017-01-01' and '2020-01-01'.

I think the correct (year, week) pair for them should be (2016, 52) and (2020, 1).

I don't know how write a SQL statement that would give these two results.

I tried:

1.

SELECT date_part('year', date_trunc('week', TIMESTAMP '2017-01-01 00:00:00')); --2016 OK
SELECT date_part('week', date_trunc('week', TIMESTAMP '2017-01-01 00:00:00')); --52 OK
SELECT date_part('year', date_trunc('week', TIMESTAMP '2020-01-01 00:00:00')); --2019 BUT I NEED 2020
SELECT date_part('week', date_trunc('week', TIMESTAMP '2020-01-01 00:00:00')); --1 OK

SELECT date_part('year', TIMESTAMP '2017-01-01 00:00:00'); --2017 BUT I NEED 2016
SELECT date_part('week', TIMESTAMP '2017-01-01 00:00:00'); --52 OK
SELECT date_part('year', TIMESTAMP '2020-01-01 00:00:00'); --2020 OK
SELECT date_part('week', TIMESTAMP '2020-01-01 00:00:00'); --1 OK

Either way, there is always something wrong, and I would possibly like something that can give the correct results. My problem is that when I am grouping by year AND week, this way sometimes I am considering more than 7 days, and I would need to avoid this.

umbe1987
  • 2,894
  • 6
  • 35
  • 63

1 Answers1

4

The week unit returns a week number based on the ISO standard. To get the equivalent year, use isoyear

SELECT date_part('isoyear', TIMESTAMP '2017-01-01 00:00:00'), 
       date_part('week', TIMESTAMP '2017-01-01 00:00:00'),
       date_part('isoyear', TIMESTAMP '2020-01-01 00:00:00'), 
       date_part('week', TIMESTAMP '2020-01-01 00:00:00');

returns:

date_part | date_part | date_part | date_part
----------+-----------+-----------+----------
     2016 |        52 |      2020 |         1