AWS Timestream Extraction functions Issue

Greetings ,

Kindly note that I have tested for your concern and on deep diving, please refer to the below mentioned analysis for the same,

If we perform the week() function or the extract() function on the year 2022 and 2023 we get the output as below,

SELECT WEEK('2022-01-01') -- 52

SELECT day_of_week('2022-01-01') -- 6 -- 1st JAN is Saturday, 6th day of the week which is a part of week in 2021

SELECT WEEK('2023-01-01') -- 52

SELECT day_of_week('2023-01-01') -- 7 -- 1st JAN is Sunday, 7th day of the week which is a part of week in 2022

But if we execute the same SQL for 2024 and 2025 we get the expected output for the week() function,

SELECT WEEK('2024-01-01') -- 1

SELECT day_of_week('2024-01-01') -- 1 -- 1st JAN Monday, 1st day of the week which is a part of week in 2024

SELECT WEEK('2025-01-01') -- 1

SELECT day_of_week('2025-01-01') -- 3 -- 1st JAN Wednesday, 3rd day of the week which is a part of week in 2025

On testing, it was observed that this depends on the week in which the date occurrs and since that week is part of the previous year it displays the number of last week of that year. Complying with that logic, since the 1st Jan of 2024 does fall in the week belonging to the year 2024, it displays as expected week number 1.

SELECT WEEK('2022-01-01') -- 52

SELECT day_of_week('2022-01-01') -- 6 -- 1st JAN Saturday, 6th day of the week which is a part of week in 2021

SELECT week_of_year('2022-01-01') -- 52

SELECT year_of_week('2022-01-01') -- 2021

SELECT year('2022-01-01') -- 2022

The international standard ISO 8601 for representation of dates and times, states that Sunday is the seventh and last day of the week. This method of representing dates and times unambiguously was first published in 1988.

https://en.wikipedia.org/wiki/Sunday#:~:text=The%20international%20standard%20ISO%208601,was%20first%20published%20in%201988.

Having said that, you can use the following workarounds to get the expected week for the above mentioned explanation due to the different/overlaping calender date placement for week between years. Since this issue is only for the first date of the first month, the following SQL gives us expected values for the week for the year.

SELECT CASE WHEN ((SELECT year('2021-01-01'))=(SELECT year_of_week('2021-01-01'))) THEN week_of_year('2021-01-01') ELSE 1 END

SELECT if((SELECT year('2022-01-01'))=(SELECT year_of_week('2022-01-01')), week_of_year('2022-01-01'), 1)

[+] https://docs.aws.amazon.com/timestream/latest/developerguide/date-time-functions.html

[+] https://docs.aws.amazon.com/timestream/latest/developerguide/conditional-expressions.html

If you need further assistance with the execution or explanation for this concern, please open a support case with AWS using the following link,

[+] https://support.console.aws.amazon.com/support/home#/case/create

Thank you!