CODEWITHSUNDEEP
mysql
Matthew
Matthew

Reputation: 129

MySQL Group Records Into Specific Chunks of Time

Hello,

I am trying to figure out an efficient way to group records into certain chunks. The records are made up of a count and time, see below example. The count is taken every minute. 

+----------+---------------+---------------+
| count_id | count_reading | count_time    |
+----------+---------------+---------------+
| 5847348  | 168           | 1358174236000 |
+----------+---------------+---------------+
| 5847347  | 342           | 1358174176000 |
+----------+---------------+---------------+
| 5847346  | 228           | 1358174116000 |
+----------+---------------+---------------+
| 5847345  | 240           | 1358174056000 |
+----------+---------------+---------------+
| 5847344  | 12            | 1358173996000 |
+----------+---------------+---------------+
| 5847343  | 0             | 1358173936000 |
+----------+---------------+---------------+
| 5847342  | 48            | 1358173876000 |
+----------+---------------+---------------+
| 5847341  | 480           | 1358173816000 |
+----------+---------------+---------------+
| 5847340  | 473           | 1358173756000 |
+----------+---------------+---------------+
| 5847339  | 380           | 1358173696000 |
+----------+---------------+---------------+

What I would like to achieve is the sum of the count over individual shifts, I have two scenarios that I am stuck on...

The result would look something like:

+---------------+--------------------------------------------+
| count_reading | count_time                                 |
+---------------+--------------------------------------------+
| 10000         | Between 0700 and now starting on the 13th  |
+---------------+--------------------------------------------+
| 20000         | Between 1900 and 0700 starting on the 12th |
+---------------+--------------------------------------------+
| 50000         | Between 0700 and 1900 starting on the 12th |
+---------------+--------------------------------------------+
| 10000         | Between 1900 and 0700 starting on the 11th |
+---------------+--------------------------------------------+

I was working on two queries that would tackle the above problem depending on the scenario. I have searched around for help on this with little luck, if anyone out there is able to help me out it would be much appreciated.

Upvotes: 0

Views: 627

Answers (1)

Marc B
Marc B

Reputation: 360702

group by can take arbitrary clauses and derived values, e.g (in pseudo-ish code):

SELECT SUM(count_reading), TIME(count_time) BETWEEN 7 and 19 AS shift
GROUP BY shift

You'll end up with two groups - shift = 1 for 7-19, and shift = 0 for 19-7`

Similarly for the 3-way shift, a bit more logic:

SELECT sum(count_reading),
    CASE WHEN TIME(count_time) BETWEEN 7 AND 14 THEN 1 ELSE
         WHEN TIME(count_time) BETWEEN 14 and 22 THEN 2
         ELSE 3 END AS shift
GROUP BY shift

the actual logic/sql syntax should be pretty straight forward.

Upvotes: 1

Related Questions