SQLite返回2013年错误的周数？

Question

我在SQLite报告中有一个简单的SQL用于计算周数

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

"我需要翻译的内容："

这段文字在2009-2012年间是正确的。但在2013年，我总是得到错误的周数。

例如：

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

返回 '09'，这是正确的。

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

返回值为 '08'，这是错误的。我们已经到了第9周。

是不是我没有理解SQLite日期时间函数中的某些内容？还是这是SQLite的一个bug？

- WebDucer

Answer 1

CL的答案对于OP的“正确”定义来说是可以的，但这并不完全符合ISO的定义。 ISO周数始终在1-53范围内（没有第0周），一年的最后3天可能会落入下一年的第1周，就像前3天可能会落入前一年的第52或53周一样。为了考虑到这些极端情况，您需要执行类似以下操作的操作：

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

作为一个旁注，SQLite的日期和时间文档确实链接到了POSIX strftime手册页面，该页面定义了%W修饰符： “一年中的周数（以周一作为一周的第一天），表示为十进制数字[00,53]。在第一个周一之前的新年所有日子都被认为是第0周。”

- srdan

---

谢谢你的回答。我会检查这个解决方案。 - WebDucer

这真的比建议的解决方案更好吗？已经测试过了吗？ - l33t

@l33t：如果你想计算ISO周数，这种方法会更好。这种方法和原始解决方案在一年中的前三天和后三天有时会有一个或多个差异。例如，2011年1月1日的ISO周数为52，而原始解决方案将其计算为0。或者2012年12月31日的ISO周数为1，而原始解决方案输出53。是的，我也进行了测试，对于50年的范围，输出与Python的datetime.isocalendar()方法的输出相同。 - srdan

非常感谢。这是完美的解决方案。 - WebDucer

Answer 2

要将SQLite的未记录周定义（第一周是具有该年第一个星期一的周，或者是具有1月7日的周）转换为ISO周定义（第一周是该年第一个星期二的周，或者是具有1月4日的周），我们让SQLite计算该年1月4日的周。如果不是一，则必须增加周数：

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

- CL.

---

谢谢帮助。有没有通用的SQL公式可以获取正确的年周数？ - WebDucer

用正确的术语来表达以下程序相关内容：第0周：这一天属于上一年度的最后一周 第1周：这一天属于本年度的第一周 第一周（ISO）从1月4日所在的那一周开始计算。 例如： 2013年01月01日=> 第1周， 2012年01月01日=> 第52周（0）， 2012年01月02日=> 第1周， 2010年01月01日=> 第53周（0）， 2010年01月04日=> 第1周。 - WebDucer

感谢您提供的出色解决方案。SQLite周计算的未记录定义是真正的问题所在。您的SQL正是我一直在寻找的。 - WebDucer

这可能导致日期所在年份的第0周。这是错误的，它必须是前一年的最后一周。请参见2021-01-01。此表达式的结果仍然需要进行大量后处理才能用于统计目的。 - ygoe

Answer 3

基于srdan的答案，这是一个完整的解决方案，包括对应于确定的周数的年份。该表达式产生一个有效的ISO 8601周规范，可以直接用于基于完整连续周的统计查询（在新年周围没有奇怪的分裂行为）。

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);

• 2020-01-01 的示例：2020-W01
• 2020-12-31 的示例：2020-W53
• 2021-01-01 的示例：2020-W53
• 2021-12-31 的示例：2021-W52
• 2022-01-01 的示例：2021-W52
• 2022-12-31 的示例：2022-W52
• 2023-01-01 的示例：2022-W52
• 2023-12-31 的示例：2023-W52
• 2024-01-01 的示例：2024-W01
• 2024-12-31 的示例：2025-W01

附注：如果出现“format”函数未定义的错误，请将其替换为旧别名printf。我无法确定它是在哪个SQLite版本中更改的。

- ygoe