MySQL Attendance Calculation

Here i have table attendances

I need result as shown below

How can i achieve this in mysql without using any programming language

Sql File is Attendances.sql

Answer

I have Achieve it my self by creating a mysql function and view

Mysql View

CREATE OR REPLACE VIEW `view_attendances` AS
    SELECT 
        `a`.`id` AS `a1_id`,
        `a`.`user_id` AS `user_id`,
        CAST(`a`.`date_time` AS DATE) AS `date`,
        `a`.`date_time` AS `in`,
        `a2`.`id` AS `a2_id`,
        `a2`.`date_time` AS `out`,
        (TIMESTAMPDIFF(SECOND,
            `a`.`date_time`,
            `a2`.`date_time`) / 3600) AS `hours`
    FROM
        (`attendances` `a`
        JOIN `attendances` `a2` ON (((`a`.`is_confirm` = 1)
            AND (`a`.`status` = 'IN')
            AND (`a2`.`id` = FN_NEXT_OUT_ATTENDANCE_ID(`a`.`user_id`, `a`.`date_time`, `a`.`status`))
            AND (a2.status = 'OUT')
            AND (CAST(`a`.`date_time` AS DATE) = CAST(`a2`.`date_time` AS DATE)))))

Mysql Function

CREATE FUNCTION `fn_next_out_attendance_id`( _user_id INT, _attendance_date_time DATETIME, _status VARCHAR(10) ) RETURNS int(11) 
BEGIN
   DECLARE _id INT(11);
SELECT
   id INTO _id 
FROM
   attendances 
WHERE
   is_confirm = 1 
   AND user_id = _user_id 
   AND date_time > _attendance_date_time 
   AND `status` <> _status 
ORDER BY
   date_time ASC LIMIT 1 ;
RETURN if (_id IS NULL, 0, _id);
END