WITH t AS ( SELECT 1 idx, 30 install, 1625813926 date UNION ALL SELECT 2, 40, 16258139551 UNION ALL SELECT 3, 50, 1625790000 UNION ALL SELECT 4, 60, 1625820000 ) SELECT DATE_FORMAT(FROM_UNIXTIME(date), '%Y-%m-%d') dt , DATE_FORMAT(FROM_UNIXTIME(date), '%H') hr , SUM(install) install FROM t WHERE date >= UNIX_TIMESTAMP('2021-07-09') AND date < UNIX_TIMESTAMP('2021-07-09') + 24*60*60 GROUP BY DATE_FORMAT(FROM_UNIXTIME(date), '%Y-%m-%d') , DATE_FORMAT(FROM_UNIXTIME(date), '%H') ;