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

Mysql – Get everyday data in hourly group using sql

I have a mysql table which has two columns id and attendancetime, I want to select count(id) hourly.

id attendancetime
1 1725256398
2 1725258398
3 1725264581
4 1725277777
5 1725277777
6 1725277777
7 1725298931
8 1725299156
9 1725299156

My expected result is:

TIME COUNT
11:00 2
01:00 1
05:00 3
11:00 3

I have tried following query:

SELECT 
    CONCAT(
        DATE(FROM_UNIXTIME(attendancetime)), ' ',
        LPAD(FLOOR(HOUR(FROM_UNIXTIME(attendancetime)) / 3) * 3, 2, '0'), ':00:00'
    ) AS TIME,
    count(id) AS COUNT
FROM operationallog
GROUP BY DATE(FROM_UNIXTIME(attendancetime)), FLOOR(HOUR(FROM_UNIXTIME(attendancetime)) / 3)
ORDER BY TIME;

Please provide mysql query to achieve this result

Tags:

2

Answers


  1. 0

    Maybe you need in

    SELECT DATE_FORMAT(FROM_UNIXTIME(attendancetime), '%Y-%m-%d %H:00') date_time,
       COUNT(*) amount
FROM tablename
GROUP BY 1;

    ?

    Login or Signup to reply.
  2. 0

    This looks both the cleanest and most efficient way:

    Integer divide the Unix epoch by 3600, and multiply by 3600 again, to truncate to the hour, and use from_unixtime() on the result to get the hour obtained converted to timestamp, then group by that expression and aggregate COUNT(DISTINCT id) on that grouping:

    WITH
indata(id,attendancetime) AS (
          SELECT 1,1725256398
UNION ALL SELECT 2,1725258398
UNION ALL SELECT 3,1725264581
UNION ALL SELECT 4,1725277777
UNION ALL SELECT 5,1725277777
UNION ALL SELECT 6,1725277777
UNION ALL SELECT 7,1725298931
UNION ALL SELECT 8,1725299156
UNION ALL SELECT 9,1725299156
)
SELECT
  from_unixtime((attendancetime DIV 3600)* 3600) AS thehour
, COUNT(DISTINCT id) AS idcount
FROM indata
GROUP BY 1;
    thehour idcount
    2024-09-02 05:00:00 1
    2024-09-02 06:00:00 1
    2024-09-02 08:00:00 1
    2024-09-02 11:00:00 3
    2024-09-02 17:00:00 3

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search