CODEWITHSUNDEEP
sqlmysql
Kramer
Kramer

Reputation: 267

MySQL Sum based on date range and time of day

I have a large set of data collected every 15 minutes. I am trying to select data between a certain time period and then within that time period divide it up by another date intervals. And within those intervals sum over a certain time period.

For example, I would like to be able to select data between 01/01/2009 and 01/01/2010 and group by date ranges 01/01/2009 - 05/01/2009, 05/02/2009 - 11/01/2009, 11/02/2009 - 01/01/2010 and then within each group select the data from time 00:00:01 - 12:00:00 and 12:00:01 - 23:59:59

SELECT SUM(Data.usage)AS sum
FROM Data.meter_id = Meter.id
WHERE Data.start_read >= '2009-01-01' 
AND Data.end_read <= '2010-01-01 23:59:59'

GROUP BY date range? Not sure how to separate the data. Thanks

Upvotes: 1

Views: 1785

Answers (1)

Bill
Bill

Reputation: 4585

To group by date ranges, I often use case statements:

Group By Case 
  When start_read between '01/01/2009' and '05/01/2010' then 'Jan-Apr 09'
  When start_read between '05/01/2009' and '11/01/2010' then 'May-Nov 09'
  ...etc

Upvotes: 2

Related Questions