为什么这个SQLite查询选择了所有日期，甚至超出了条件范围？

Question

我正在运行sqlite来选择两个日期范围内的数据以生成销售报告。为了从两个日期之间选择数据，我使用以下语句：

SELECT * FROM test WHERE date BETWEEN "11/1/2011" AND "11/8/2011";

这个语句获取了所有日期，即使超出了条件。你看到输入的日期格式与我得到的格式相同。我不确定问题出在哪里。

- Zombian

---

一个相关的小建议：如果可能的话，总是使用yyyy/MM/dd的格式。这将避免讨厌的区域差异问题，这可能是您遇到的问题。 - Haedrian

Answer 1

SQLite要求日期格式为YYYY-MM-DD。由于您数据库中的数据和查询语句中的字符串不符合该格式，因此SQLite可能会将您的“日期”视为字符串。

- Eric Petroelje

---

更改日期格式后，我再次运行查询语句： 'SELECT date FROM test WHERE date BETWEEN "2011-11-1" AND "2011-11-2";' 结果返回的却是2011年11月16日:/ - Zombian

那是每个月的第10天和第20天。请尝试使用BETWEEN '2011-11-01' AND '2011-11-02'。 - Larry Lustig

如果你正在处理Android SQLite，请确保它是小写的yyyy。 - Ajji

SQLite将日期视为字符串，无论它们的格式如何。使用YYYY-MM-DD的原因是因为文本排序顺序与时间排序顺序相同。任何在较小字段之前具有较大字段的填充数字格式都可以工作。 - Captain Man

Answer 2

将您的数据更改为这些格式，以使用sqlite日期时间格式。

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

SELECT * FROM test WHERE date BETWEEN '2011-01-11' AND '2011-08-11'

- Utku Yıldırım

---

虽然这个想法是正确的，但考虑到返回的数据也是以“MM/DD/YYYY”格式呈现的，我怀疑这仍然行不通，因为他数据库中的数据将被视为字符串而不是日期。 - Eric Petroelje

@EricPetroelje 是的 - 但是一旦您将数据库中的格式与查询中的格式匹配，它就会起作用。看起来它后来被编辑以包括更新后的查询 - 虽然查询需要使用单引号而不是双引号。 - vapcguy

Answer 3

在SQLite中另一种选择日期的方法是使用强大的 strftime 函数：

SELECT * FROM test WHERE strftime('%Y-%m-%d', date) BETWEEN "11-01-2011" AND "11-08-2011"

根据https://sqlite.org/lang_datefunc.html，以下内容是等价的：

date(...)

strftime('%Y-%m-%d', ...)

但是如果你想要更多的选择，你可以得到它。

- Andrei Drynov

---

引号应该是单引号，但查询应该有效。 - vapcguy

这仍然不起作用，strftime（'%Y-%m-%d'，'11/1/2011'）返回null，因为这不是它接受的格式。此外，如果您执行了BETWEEN '11-01-2011' AND '12-01-2011'，您将无法获取2011年11月的内容，而是会得到许多不同年份的内容。字符串'11-01-9999'也在其中。 - Captain Man

Answer 4

SELECT *
FROM TableName
WHERE julianday(substr(date,7)||'-'||substr(date,4,2)||'-'||substr(date,1,2)) BETWEEN julianday('2011-01-11') AND julianday('2011-08-11')

请注意，我使用的格式是：dd/mm/yyyy。如果你使用的是d/m/yyyy，请在substr()中进行更改。

- TROUZINE Abderrezaq

---

很好的切片使用！点赞。请注意，您不需要使用julianday()函数，因为使用该函数假定您的日期是在UTC时间下，因为julianday()函数是基于GMT时间计算的，所以如果您的日期是在本地时间下，您可能会包括一些您不想要的日期，并且漏掉一些您想要的日期。您只需执行以下查询语句即可：SELECT * FROM TableName WHERE substr(date,7)||'-'||substr(date,4,2)||'-'||substr(date,1,2) BETWEEN '2011-01-11' AND '2011-08-11'，它应该可以正常工作。 - vapcguy

Answer 5

或者你可以使用 date 函数将字符串转换为日期格式，即使日期在数据库中以 TEXT 存储。

像这样（最可行的变体）：

SELECT * FROM test WHERE date(date) 
BETWEEN date('2011-01-11') AND date('2011-08-11')

- adudakov

---

我认为这个解决方案的问题在于你没有指定文本的日期格式（例如，如果日期字符串存储为“MM/DD/YYYY”）。我尝试了这种方法，但对我来说不起作用，我认为这就是原因。 - jersey bean

这个并不是你想象中的那样。在SQLite中，日期函数返回一个文本日期，例如 2011-01-11，因此调用 date('2011-01-11) 返回输入值。使用 SELECT * FROM test WHERE date BETWEEN '2011-01-11' and '2011-08-11'; 将会产生相同的效果。但是，以 OP 中的格式调用它 (date('11/1/2011')) 将不起作用，因为它不接受这种格式。 - Captain Man

Answer 6

SQLite没有日期的概念，它只将其视为文本。在SQLite中进行比较时，实际上是进行字符串比较。您可以从官方文档中了解更多信息。

当比较两个TEXT值时，会使用适当的排序序列来确定结果。

任何数字（即不使用像“May”这样的单词）格式的日期，只要填充并按从最大字段到最小字段的顺序排列即可。 "2021-05-07"（5月7日）在 "2021-05-09"（5月9日）之前。因此，如果您使用“yyyy-mm-dd”格式，则可以设置。 "yyyy/mm/dd"和"yyyymmdd"也完全可以。 （有关“可排序”日期格式的更好措辞，请参见RFC 3339第5.1节。）

使用“yyyy-mm-dd”格式的原因是这是SQLite内置的date使用的格式。

- Captain Man

Answer 7

特别感谢Jeff和vapcguy，你们的互动真的很鼓舞人心。

这是一个更复杂的语句，当'/'之间的长度未知时非常有用：

SELECT * FROM tableName
WHERE julianday(
    substr(substr(date, instr(date, '/')+1), instr(substr(date, instr(date, '/')+1), '/')+1)
||'-'||
    case when length(
    substr(date, instr(date, '/')+1, instr(substr(date, instr(date, '/')+1),'/')-1)
    )=2
    then
    substr(date, instr(date, '/')+1, instr(substr(date, instr(date, '/')+1), '/')-1)
    else
    '0'||substr(date, instr(date, '/')+1, instr(substr(date, instr(date, '/')+1), '/')-1)
    end
||'-'||
    case when length(substr(date,1, instr(date, '/')-1 )) =2
    then substr(date,1, instr(date, '/')-1 )
    else
    '0'||substr(date,1, instr(date, '/')-1 )
    end
) BETWEEN julianday('2015-03-14') AND julianday('2015-03-16') 

- TROUZINE Abderrezaq

---

谢谢夸奖，你的子字符串能力真是了不起。不过，我对另一篇帖子中关于julianday()的评论仍然适用。这基本上与其他答案相同，只是你加入了条件逻辑来添加前导零以表示单个数字的日期和月份。可能应该将其添加到其他答案中，或者用此编辑替换其他答案。虽然如此，我还是点了赞，因为它更正确。 - vapcguy

Answer 8

假设你正在为某个报告准备数据。那么整个过程看起来会类似于这样。

--add column with date in ISO 8601 
ALTER TABLE sometable ADD COLUMN DateInISO8601;

--update the date from US date to ISO8601 date 
UPDATE sometable 
SET DateInISO8601 = substr([DateInUSformat],length([DateInUSformat])+1, -4) 
  || '-' || 
  substr('00' || [DateInUSformat],instr('00' || [DateInUSformat],'/'),-2)  
  || '-' || 
  substr('00' || rtrim(substr([DateInUSformat],instr([DateInUSformat],'/')+1,2),'/'),-2,2);

SELECT DateInISO8601 
FROM sometable 
WHERE DateInISO8601 BETWEEN '2022-02-02' AND '2022-02-22';

当然你可以在运行时完成所有操作，但如果有选择的话--不要这么做。默认使用ISO日期格式，在进出SQLite数据库时进行转换。

- Pepik

Answer 9

将变量放在Where条件中，并使用“BETWEEN”解析两个日期：

SELECT * FROM emp_master

-> 如果你有像dd/mm/yyyy这样简单的日期格式，那么，

   WHERE joined_date BETWEEN '01/03/2021' AND '01/09/2021';

-> 如果您有日期格式，例如yyyy/mm/dd，则：

   WHERE joined_date BETWEEN '2021/03/01' AND '2021/09/01';

☻♥ 完成 保留代码。

- Nikhil S Marathe