sql - 在SQL中按DATE分组，返回nil

Question

我正在努力理解为什么在Rails的SQLite中的datetime列上按DATE分组会返回nil。我想返回在startdate字段中具有值的用户计数（按日期分组）。

有人可以帮忙吗？删除以下查询的DATE方面会返回预期的结果。请参阅下面的不同查询和结果：

User.where("startdate IS NOT NULL").group("DATE(startdate)").count

给

SELECT COUNT(*) AS count_all, DATE(startdate) AS date_startdate 
FROM "users" 
WHERE (startdate IS NOT NULL) 
GROUP BY DATE(startdate)

=> {nil=>10}

我期望的是一系列日期（例如{“ 2013-06-04” => 3，“ 2013-06-10” => 4，
“ 2013-06-11” => 3}。在下面没有DATE运算符的查询中，结果比我期望的要多，但是显然我也不想按时间分组（在我的实际数据库中，同一日期有不同的时间）。

User.where("startdate IS NOT NULL").group("(startdate)").count

给

SELECT COUNT(*) AS count_all, (startdate) AS startdate 
FROM "users" 
WHERE (startdate IS NOT NULL) 
GROUP BY (startdate)  

=> {"2013-06-04 17:27:30.553802+0000"=>3, "2013-06-10 09:24:06.207327+0000"=>4,
"2013-06-11 12:20:37.745819+0000"=>3}

Answer 1

SQLite期望您的数据最近的date formated string是YYYY-MM-DD HH:MM:SS.SSS。

如果所有日期都在同一时区+0000中，则可以完全忽略DATE函数并比较子字符串：

User.where("startdate IS NOT NULL").group("SUBSTR(startdate,1,10)").count

无论如何，SQLite不会执行日期比较。实际上， DATE函数总是返回 TEXT。

如果您处理不同的时区（最好在应用程序中进行处理），则可以使用 Date/time functions modifiers进行其他计算。

编辑：

作为 @chuck.p.lee correctly corrected me，最接近的格式现在是 YYYY-MM-DD HH:MM:SS.SSS+HH:MM。因此，使用一些字符串操作（截短毫秒级的部分）将日期转换为此非常容易：

SUBSTR(startdate, 1, 23) || SUBSTR(startdate, 27, 3) || ':' || SUBSTR(startdate, 30,2)

现在，您的查询可能正确编写为：

User.where("startdate IS NOT NULL").group("DATE(SUBSTR(startdate, 1, 23) || SUBSTR(startdate, 27, 3) || ':' || SUBSTR(startdate, 30,2))").count