Question 20486 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL group by custom interval

I have a MySQL table, with datetime values in each row.

I try to group by two intervals:

  1. Date between now and now – 1hour
  2. Date betwwen now – 1 hour and now – 2 hour

What i do?

SELECT MIN(date), MAX(date), count(stats.ID) as c FROM stats WHERE date BETWEEN (NOW() - INTERVAL 2 HOUR) AND NOW() GROUP BY UNIX_TIMESTAMP(date) DIV 7200
MIN(DATE) MAX(DATE) count
2023-03-11 18:45:15 2023-03-11 18:59:59 150
2023-03-11 19:00:01 2023-03-11 19:45:15 250

and i try this:

SELECT count(ID),  MIN(date), MAX(date), sec_to_time(time_to_sec(date)- time_to_sec(DATE)%(60*60)) as intervals from stats WHERE date BETWEEN (NOW() - INTERVAL 2 HOUR) AND NOW()group by intervals
MIN(DATE) MAX(DATE) count
2023-03-11 18:45:15 2023-03-11 18:59:59 150
2023-03-11 19:00:00 2023-03-11 19:59:59 250
2023-03-11 20:00:00 2023-03-11 19:45:15 250

Expected result

MIN(DATE) MAX(DATE) count
2023-03-11 18:45:15 2023-03-11 19:45:15 150
2023-03-11 19:45:15 2023-03-11 20:45:15 250
Tags:

2

Answers


  1. 0 
    SELECT AGGREGATE_FN( CASE WHEN datetime_column BETWEEN NOW() AND NOW - INTERVAL 1 HOUR
                          THEN value_column
                          END) AS agg_now,
       AGGREGATE_FN( CASE WHEN datetime_column BETWEEN NOW() - INTERVAL 1 HOUR AND NOW - INTERVAL 2 HOUR
                          THEN value_column
                          END) AS agg_before
FROM data_source
WHERE datetime_column BETWEEN NOW() AND NOW - INTERVAL 2 HOUR;
    Login or Signup to reply.
  2. 0

    Some sample data and expected output would be helpful, but can’t you just do:

    SELECT MIN(`date`), MAX(`date`), COUNT(stats.ID) AS c,
    CASE
        WHEN `date` BETWEEN NOW() - INTERVAL 2 HOUR AND NOW() - INTERVAL 1 HOUR
            THEN 1
        WHEN `date` BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
            THEN 2
    END AS grp
FROM stats
WHERE `date` BETWEEN NOW() - INTERVAL 2 HOUR AND NOW()
GROUP BY grp

    or even just:

    SELECT MIN(`date`), MAX(`date`), COUNT(stats.ID) AS c,
    IF(`date` <= NOW() - INTERVAL 1 HOUR, 1, 2) AS grp
FROM stats
WHERE `date` BETWEEN NOW() - INTERVAL 2 HOUR AND NOW()
GROUP BY grp;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search