SQLite 返回错误的 2013 周数? [英] SQLite return wrong week number for 2013?

问题描述

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

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

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

2009-2012 年是正确的.2013 年我总是弄错周数.

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

例如

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

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

return '09' and this is correct.

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

返回'08'，这是错误的.我们有第 9 周.

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

SQLite 日期时间函数中有什么我不明白的地方吗?还是SQLite的bug?

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

推荐答案

CL 的回答适用于 OP 对正确"的定义，这与 ISO 定义不太一样.ISO 周数始终在 1-53 范围内(没有第 0 周)，一年的最后 3 天可能属于下一年的第 1 周，就像前 3 天可能属于第 52 周或第 53 周一样前一年.要考虑这些极端情况，您需要执行以下操作:

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;

附带说明，SQLite 的日期和时间 文档确实链接到 POSIX strftime 手册页，它定义了 %W 修饰符为:一年中的周数(星期一为一周的第一天)，十进制数 [00,53].新年中第一个星期一之前的所有天数都被视为第 0 周."

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."

这篇关于SQLite 返回错误的 2013 周数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！