CODEWITHSUNDEEP
mysqlmysql-5.7
Maria
Maria

Reputation: 121

MySQL 5.7 Group by specific 30 minute interval

I have a MySQL 5.7 table, with time stamps on each row. My goal is to calculate the values ​​of the some_id column and group them according to the specified 30-minute interval. And display only those intervals where count more than 0. input:

timestamp                 some_id
--------------            ------
2019-01-19 05:30:12       4
2019-01-19 05:40:12       8
2019-01-19 15:37:40       2
2019-01-20 01:57:38       2
2019-01-20 07:10:07       4
2019-01-20 22:10:38       2
2019-01-21 08:35:55       4

expected:

interval             COUNT(some_id)
-------------       -----------
05:30:00 - 06:00:00     2
07:00:00 - 07:30:00     1
08:30:00 - 09:00:00     1
22:00:00 - 22:30:00     1
etc..........

I have tried implementing the solution presented here MySQL Group by specific 24 hour interval - but without success.

my try

I'm not sure that this is the right solution

SELECT CONCAT(
          DATE_FORMAT(table.timestamp, "%H:"),
          IF("30">MINUTE(table.timestamp), 
          (CONCAT("00-",DATE_FORMAT(DATE_ADD(table.timestamp, INTERVAL 60 MINUTE), "%H:"),"30")), 
          (CONCAT("30-",DATE_FORMAT(DATE_ADD(table.timestamp, INTERVAL 30 MINUTE), "%H:"),"00")))) AS time_period,
            COUNT(*)
        FROM table
        GROUP BY time_period;

Upvotes: 3

Views: 1006

Answers (3)

claudiodfc
claudiodfc

Reputation: 146

I took what @symcbean said but tunned it a bit in order to give what you wished:

SELECT CONCAT(
  DATE_FORMAT(timestamp, "%H:00 - "),
  IF("00">MINUTE(timestamp), CONCAT(HOUR(timestamp), ":00"), CONCAT(HOUR(timestamp), ":30"))
) AS Time,
COUNT(*)
FROM mysql_24h_cycle
GROUP BY CONCAT(
 DATE_FORMAT(timestamp, "%H:"),
 IF("30">MINUTE(timestamp), CONCAT(HOUR(timestamp), ":00"), CONCAT(HOUR(timestamp), ":30"))
)

Here you can see the query and the output

This is the data used

Upvotes: 0

Harly Hallikas
Harly Hallikas

Reputation: 620

The fastest and simplest solution would be this:

CREATE TABLE IF NOT EXISTS `events` (
  `id` int unsigned NOT NULL,
  `timestamp` DATETIME NOT NULL,
  PRIMARY KEY (`timestamp`)
);
INSERT INTO `events` (`timestamp`, `id`) VALUES
   ('2019-01-19 05:30:12', 4),
   ('2019-01-19 06:20:12', 4),
   ('2019-01-19 15:37:40', 2),
   ('2019-01-20 01:57:38', 2),
   ('2019-01-20 07:10:07', 4),
   ('2019-01-20 22:10:38', 2),
   ('2019-01-21 08:35:55', 4);

And the query:

SELECT 
  DATE(timestamp), HOUR(timestamp), SUM(id)
FROM 
  events
GROUP BY 1,2 ORDER BY 1,2;

which produced

this result

http://sqlfiddle.com/#!9/fadd28/5

EDIT: Whoops, missed the 30-minute interval requirement. But I think you get the point. You can play with my solution at the link above. :)

But if your database supports WINDOW functions, even better.

Also, for such purpose I'd personally create an aggregation table which will contain hourly counters and is being updated during INSERT using TRIGGER.

Upvotes: 1

symcbean
symcbean

Reputation: 48357

The link you provided has a very esoteric solution. Try:

SELECT CONCAT(
  DATE_FORMAT(mydate, "%Y-%m-%d %H:"),
  IF("30">MINUTE(mydate), "00", "30")
) AS time_bin,
COUNT(*)
FROM mytable
GROUP BY CONCAT(
 DATE_FORMAT(mydate, "%Y-%m-%d %H:"),
 IF("30">MINUTE(mydate), "00", "30")
)

Upvotes: 2

Related Questions