Thread: Year 2024: Extracting week from date gives wrong results for the lasttwo days of the year

Year 2024: Extracting week from date gives wrong results for the lasttwo days of the year

From

Michael Monscheuer

Date:

04 July 2018, 19:55:54

Team,

Extracting the week from a date gives wrong results for the last two days of 2024.

SELECT EXTRACT (WEEK FROM DATE '20241230');

or

SELECT EXTRACT (WEEK FROM DATE '20241231');

both result in a value of  1
which is wrong, obviously...

The expected result is 53

The results for the year 2020 are correct:
SELECT EXTRACT (WEEK FROM DATE '20201230');
results to 53

Version:
PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
on Amazon RDS

Greetings from Hamburg, DE

MiMo

Re: Year 2024: Extracting week from date gives wrong results for thelast two days of the year

From

Jeff Janes

Date:

04 July 2018, 20:13:43

On Wed, Jul 4, 2018 at 9:55 AM, Michael Monscheuer <mm@digiperience.de> wrote:

Team,

Extracting the week from a date gives wrong results for the last two days of 2024.

SELECT EXTRACT (WEEK FROM DATE '20241230');

or

SELECT EXTRACT (WEEK FROM DATE '20241231');

both result in a value of 1
which is wrong, obviously...

Not wrong. See the docs:

By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year.

So the first week of 2025 contains some days from 2024.

Cheers,

Jeff

Re: Year 2024: Extracting week from date gives wrong results for thelast two days of the year

From

Félix GERZAGUET

Date:

04 July 2018, 20:14:13

On Wed, Jul 4, 2018 at 3:55 PM, Michael Monscheuer <mm@digiperience.de> wrote:

Extracting the week from a date gives wrong results for the last two days of 2024.

SELECT EXTRACT (WEEK FROM DATE '20241230');

or

SELECT EXTRACT (WEEK FROM DATE '20241231');

both result in a value of 1
which is wrong, obviously...

The expected result is 53

It is correct as per documentation (and the ISO 8601 standard):

Please look at: https://www.postgresql.org/docs/10/static/functions-datetime.html

then looking for the week field you will see:

The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year.

In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005, while 2012-12-31 is part of the first week of 2013. It's recommended to use the isoyear field together with week to get consistent results.

Regards,

Félix

Re: Year 2024: Extracting week from date gives wrong results for thelast two days of the year

From

Gavin Flower

Date:

04 July 2018, 23:21:02

On 05/07/18 01:55, Michael Monscheuer wrote:
> Team,
>
> Extracting the week from a date gives wrong results for the last two 
> days of 2024.
>
> SELECT EXTRACT (WEEK FROM DATE '20241230');
>
> or
>
> SELECT EXTRACT (WEEK FROM DATE '20241231');
>
> both result in a value of  1
> which is wrong, obviously...
>
> The expected result is 53
>
> The results for the year 2020 are correct:
> SELECT EXTRACT (WEEK FROM DATE '20201230');
> results to 53
>
> Version:
> PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 
> 20140911 (Red Hat 4.8.3-9), 64-bit
> on Amazon RDS
>
> Greetings from Hamburg, DE
>
> MiMo
>
>
Weeks start on Monday.

Weeks belong to the year in which they have the most days.


Cheers,
Gavin