获得两个给定日期之间的总和 [英] get the SUM between two given dates

问题描述

如果我想获得某个日期范围内的total_consumption，我该怎么做?

if i want to get the total_consumption over a range of dates, how would i do that?

我认为我可以做到:

SELECT id, SUM(consumption) 
FROM consumption_info 
WHERE date_time BETWEEN 2013-09-15 AND 2013-09-16 
GROUP BY id;

无论如何返回:空集，2条警告(0.00秒)

however this returns: Empty set, 2 warnings(0.00 sec)

---------------------------------------
id | consumption |  date_time          |        
=======================================|
1  |      5      | 2013-09-15 21:35:03 |
2  |      5      | 2013-09-15 24:35:03 |
3  |      7      | 2013-09-16 11:25:23 |
4  |      3      | 2013-09-16 20:15:23 |
----------------------------------------

有什么想法我在这里做错了吗?

any ideas what i'm doing wrong here?

预先感谢

推荐答案

您在日期字符串周围缺少引号:WHERE子句实际上应写为...

You're missing quotes around the date strings: the WHERE clause should actually be written as...

BETWEEN '2013-09-15' AND '2013-09-16'

具有讽刺意味的是，2013-09-15 是是有效的SQL表达式-表示2013 minus 09 minus 15.显然，相应结果之间没有日期.因此返回空集

The irony is that 2013-09-15 is a valid SQL expression - it means 2013 minus 09 minus 15. Obviously, there's no date lying in between the corresponding results; hence an empty set in return

但是这里可能还有另一个更细微的错误:您可能应该使用此子句...

Yet there might be another, more subtle error here: you probably should have used this clause...

BETWEEN '2013-09-15 00:00:00' AND '2013-09-16 23:59:59'

...代替.如果未明确设置时间，则两个日期都将其设置为"00:00:00"(因为此处比较了DATETIME值).

... instead. Without setting the time explicitly it'll be set to '00:00:00' on both dates (as DATETIME values are compared here).

虽然开始日期显然没问题，但结束日期不是这样-除非当然要排除当天的任何时间但午夜的所有记录，实际上是理想的结果.

While it's obviously ok for the starting date, it's not so for the ending one - unless, of course, exclusion of all the records for any time of that day but midnight is actually the desired outcome.

这篇关于获得两个给定日期之间的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！