SQLite return wrong week number for 2013?

Question

I have a simple SQL for calculating week number in my reports on SQLite

SELECT STRFTIME('%W', 'date_column')

It was correct for 2009-2012. In 2013 I got always the wrong week number.

For example

SELECT STRFTIME('%W', '2012-02-28')

return '09' and this is correct.

SELECT STRFTIME('%W', '2013-02-28')

return '08' and this is wrong. We have the 9th week.

Is there something in SQLite date time functions that I don't understand? Or is it a bug of SQLite?

Huoh

Answer 1

CL's answer works fine for OP's definition of "right", which is not quite the same as ISO definition. ISO week numbers are always in the range 1-53 (no week 0), and the last 3 days of a year may fall into Week 1 of the following year, just like the first 3 days may fall into Week 52 or 53 of the preceding year. To take these corner cases into account, you need to do something like:

SELECT
    (strftime('%j', date(MyDate, '-3 days', 'weekday 4')) - 1) / 7 + 1 AS ISOWeekNumber
FROM MyTable;

As a side note, SQLite's Date and Time documentation does link to the POSIX strftime man page, which defines %W modifier as: "week number of the year (Monday as the first day of the week) as a decimal number [00,53]. All days in a new year preceding the first Monday are considered to be in week 0."

Sherwin

Thanks for the answer. I will check the solution. – Huoh

Is this really better than the suggested solution? Tested? – Loreanloredana

@l33t: It is "better" if you want to calculate ISO week numbers. This and the original solution differ sometimes in one or more of the last 3 and the first 3 days of a year. For example, ISO week number of 01/01/2011 is 52, the original solution calculates it as 0. Or ISO week of 12/31/2012 is 1, the original solution outputs 53. And yes, I tested it too, for a range of 50 years, and the ouput is identical to the output of python's datetime.isocalendar() method. – Sherwin

Answer 2

To convert from SQLite's undocumented week definition (first week is the week with the year's first Monday in it, or the week with 7 January in it) to the ISO week definition (first week is the week with the year's first Tuesday in it, or the week with 4 January in it), we let SQLite compute the week of the year's 4 January. If that is not one, we have to increase the week number:

SELECT strftime('%W', MyDate)
       + (1 - strftime('%W', strftime('%Y', MyDate) || '-01-04'))
FROM MyTable

Aliaalias

Thanks for help. Is there a common sql formula to get the right week number of the year? – Huoh

Im mean the following with the right. Week number 0: the day belongs to the last week of the year before Week number 1: the day belongs to the first week of the year The first week (ISO) starts with the week where the 4th of january belongs to. e.g.: 01.01.2013 => 1st week, 01.01.2012 => 52nd week (0), 02.01.2012 => 1st week, 01.01.2010 => 53rd week (0), 04.01.2010 => 1st week – Huoh

Thanks for the great solution. The undocumented definition of the SQLite week calculation was the real problem. Your SQL is exactly what I was finding for. – Huoh

This can result in a week number "0" of the year of the date. That's wrong, it must be the last week of the preceeding year. See 2021-01-01. The result of this expression must still be heavily post-processed to be usable for statistical purposes. – Ransdell

Answer 3

Based on srdan's answer, this is a complete solution that includes the year number that corresponds to the determined week number. This expression yields a valid ISO 8601 week specification and can be used as-is in statistical queries that are based on full continuous weeks (without weird split behaviour around new years).

select format('%04d', strftime('%Y', date(_time_, '-3 days', 'weekday 4'))) || '-W' ||
    format('%02d', (strftime('%j', date(_time_, '-3 days', 'weekday 4')) - 1) / 7 + 1);

• Example for 2020-01-01: 2020-W01
• Example for 2020-12-31: 2020-W53
• Example for 2021-01-01: 2020-W53
• Example for 2021-12-31: 2021-W52
• Example for 2022-01-01: 2021-W52
• Example for 2022-12-31: 2022-W52
• Example for 2023-01-01: 2022-W52
• Example for 2023-12-31: 2023-W52
• Example for 2024-01-01: 2024-W01
• Example for 2024-12-31: 2025-W01

PS: If you get the error that the format function was not defined, replace it with the older alias printf. I couldn't find out in which SQLite version it was changed.

Ransdell