CODEWITHSUNDEEP
mysqldatetimeintervals
egariM
egariM

Reputation: 185

mysql sum query between interval

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!

Upvotes: 0

Views: 695

Answers (3)

Ramin Taghizada
Ramin Taghizada

Reputation: 125

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

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

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

Upvotes: 1

Just_Do_It
Just_Do_It

Reputation: 821

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

Upvotes: 0

Related Questions