mysql sum query between interval

Question

I have a little odd problem with a mysql query :)

This is my table:

mysql> describe meteo;

| Field     | Type        | Null | Key | Default | Extra          |

| id        | int(11)     | NO   | PRI | NULL    | auto_increment |

| name      | varchar(32) | NO   |     | NULL    |                |

| date      | datetime    | NO   |     | NULL    |                |

| value     | int(8)      | NO   |     | NULL    |                |

My goal is this: create a query that sum all value from 14:00 to 16:00 stepping by 5 or 10 minutes.

I do something that is near the solution doing this N time from (14:00 to 16:00)

SELECT sum(value)  FROM meteo WHERE date>='2016-09-9 14:30:00' AND date<='2016-09-9 14:35:00'

SELECT sum(value)  FROM meteo WHERE date>='2016-09-9 14:35:00' AND date<='2016-09-9 14:40:00'

... and so on...

Exists a query that do this only giving the interval range? I whould like to have something like this in the output:

2016-09-9 14:30:00 2016-09-9 14:35:00  176

2016-09-9 14:35:00 2016-09-9 14:40:00  2312

...

Thanks for help!

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Nov 04 '16 at 20:38

score 1 · Answer 1 · edited May 23 '17 at 10:32

1

You could use a table min_interval

  id begin                 END
  1  2016-09-9 14:30:00    2016-09-9 14:35:00
  2  2016-09-9 14:35:00    2016-09-9 14:40:00
  3  2016-09-9 14:40:00    2016-09-9 14:45:00

You can look this post to see how generate a list of day, easy to change for minutes.

generate days from date range

and then

 SELECT m.begin, m.end, SUM(t.value)
 FROM min_interval m 
 LEFT JOIN meteo t
   ON t.date >= m.begin
  AND t.date < m.end
 GROUP BY m.begin, m.end

edited May 23 '17 at 10:32

Community

1
1

answered Nov 04 '16 at 17:37

Juan Carlos Oropeza

47,252
12
78
118

Thank for reply, but it's a little bit hard generate a table each time for a date! Maybe I should change way and do it into python! – egariM Nov 06 '16 at 10:01
You dont need a table, you can create a subquery. Did you check the generate days link? – Juan Carlos Oropeza Nov 06 '16 at 14:00

score 0 · Answer 2 · answered Nov 04 '16 at 17:38

0

Well you can use mysql date_add() function like this:

SELECT sum(value)  FROM meteo WHERE date BETWEEN DATE_ADD(date,INTERVAL 5 MINUTE) AND date

answered Nov 04 '16 at 17:38

Just_Do_It

821
7
20

1

`sum all value from 14:00 to 16:00 stepping by 5 or 10 minutes.` ?? – Juan Carlos Oropeza Nov 04 '16 at 17:41
Sorry mis-read the question, this will not include the stepping process, apologies. – Just_Do_It Nov 04 '16 at 17:51

score 0 · Answer 3 · answered Nov 04 '16 at 20:45

0

Select sum(value) from Meteo Where date >= currentDate 13:55 and date <= currentDate 16:10;

answered Nov 04 '16 at 20:45

Ramin Taghizada

125
2
9

mysql sum query between interval

3 Answers3