Group Records by 30 minutes + start it from 08:00 til 07:00 ( 24 hours )

i never meet case study like this feature, but i have a model called "Ticket", and i want to show the ticket created by 30 minutes in a day, started from 08:00, 08:30, and so on, until 07:30.

i already create a function that grouped the Tickets but it grouping by 1 hour. this is the function

public static function get_busy_hour($type = NULL, $filter = NULL){
        $sql = "SELECT Hours.Hour_num, Hours.Hour, COALESCE(total_s, 0) AS Total
        FROM
        (SELECT DATE_FORMAT(now(), '%h%p') AS Hour, DATE_FORMAT(now(), '%H') AS Hour_num
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 3 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 3 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 4 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 4 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 5 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 5 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 6 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 6 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 7 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 7 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 8 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 8 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 9 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 9 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 10 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 10 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 11 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 11 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 12 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 12 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 13 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 13 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 14 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 14 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 15 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 15 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 16 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 16 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 17 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 17 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 18 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 18 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 19 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 19 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 20 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 20 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 21 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 21 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 22 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 22 HOUR), '%H')
            UNION SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 23 HOUR), '%h%p'), DATE_FORMAT(DATE_SUB(now(), INTERVAL 23 HOUR), '%H')
        ) AS Hours
        LEFT JOIN 
            (SELECT *, COUNT(*) AS total_s, DATE_FORMAT(created_at,'%h%p') AS Hour_j
            FROM it_tickets";

        if($type == 1){
            //date
            $date = explode("|", $filter);
            $sql .= ' WHERE created_at BETWEEN "'.date_format(date_create($date[0]), 'Y-m-d').' 00:00:00"'.' AND "'.date_format(date_create($date[1]), 'Y-m-d').' 23:59:59"';
        }
        elseif($type == 2){
            //day
            if($filter != ''){
                $sql .= ' WHERE DAYNAME(created_at) = "'.$filter.'"';
            }
        }

        $sql .= " GROUP BY HOUR(created_at)) jj ON Hours.Hour = jj.Hour_j ORDER BY Hour_num";


        $query = DB::select(DB::raw($sql));
        return $query;
    }

what is exactly need to be edit from this function to group it by 30 minutes interval in a days sorry very bad english. and thank you for answering