Closed
Description
According to ISO 8601 standard, the week of an year is calculated following few rules:
- Weeks start with Monday
- Each week's year is the Gregorian year in which the Thursday falls
- The definition for week 01 is the week with the Gregorian year's first Thursday in it. The following statements are true, as well:
- It is the first week with a majority (4 or more) of its days in January.
- Its first day is the Monday nearest to 1 January.
- It has 4 January in it. Hence the earliest possible first week extends from Monday 29 December (previous Gregorian year) to Sunday 4 January, the latest possible first week extends from Monday 4 January to Sunday 10 January.
- It has the year's first working day in it, if Saturdays, Sundays and 1 January are not working days.
Examples:
- January 1st, 2005 was on a Saturday. According to Gregorian calendar, it's week 1 of year 2005. According to ISO standard, it's week 53 of year 2004 because the first week of 2005 starts on Monday, January 3rd.
- December 31st, 2007 was on a Monday. According to Gregorian calendar, it's the last week of year 2007. According to ISO standard, it's week 1 or year 2008 because it starts on Monday and because most of its days are in January.
In case of ES SQL, we have:
SELECT ISO_WEEK_OF_YEAR(CAST('2005-01-01T00:00:00Z' AS DATETIME)) isow2005, WEEK(CAST('2005-01-01T00:00:00Z' AS DATETIME)) AS w2005, ISO_WEEK_OF_YEAR(CAST('2007-12-31T00:00:00Z' AS DATETIME)) isow2007, WEEK(CAST('2007-12-31T00:00:00Z' AS DATETIME)) AS w2007
Which results in
isow2005 | w2005 | isow2007 | w2007
---------------+---------------+---------------+---------------
1 |1 |53 |1
And it should have resulted in 53 |1 |1 |53