get the SUM between two given dates

Question

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

I thought i could do:

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

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?

thanks in advance

Answer 1

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

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

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'

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

Answer 2

SELECT SUM(consumption) 
FROM consumption_info 
WHERE date_time >= 2013-09-15 AND date_time <= 2013-09-16;

or

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

Answer 3

Its better to use CAST when comparing the date function.

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