Help with SELECT and TIMEDIFF

[Eneida Woods]

Hello,

I am building a database that collects entry and exit of employees. Daily, the worker will log in when arriving at the workplace, log out for lunch, log in when returning from lunch, and log out to exit the workplace. There are just two tables in the db: tb_user and tb_logging_system.

Below, it is the structure of my tb_logging_system:

login id INT AUTO_INCREMENT (PK)
login_user_id INT NOT NULL (FK)
login_date_time_log_entry DATETIME NOT NULL
login_date_time_log_exit DATETIME NOT NULL

I have created the following SELECT to calculate the number of hours worked by each employee:

SELECT user_name,
TIMEDIFF(login_date_time_log_exit,login_date_time_log_entry) as Hours_Worked
FROM tb_user
INNER JOIN logging_system ON user_ID = login_user_id;

Sample data:

Name/ Entry / Exit / Hours_Worked
===========================================================
John / 2005-09-05 08:30:27 / 2005-09-05 12:19:33 / 03:49:06
John / 2005-09-05 13:29:35 / 2005-09-05 18:08:37 / 04:39:02
Mary / 2005-09-05 08:16:44 / 2005-09-05 12:40:09 / 04:23:25
Mary / 2005-09-05 14:22:00 / 2005-09-05 18:04:09 / 03:42:09

However, when I tried to implement a little further on this query, I didn’t achieve the results expected. The query is not grouping my results by employee or calculating the sum for the day of work. I have tried everything, including CAST( ) to convert the result of the TIMEDIFF to time; nevertheless, all my efforts have been unsuccessful. Below, it is the problematic query:

SELECT user_name,date(login_date_time_entry) as Date_Log,
SUM(TIMEDIFF(login_date_time_log_exit,login_date_time_log_entry)) as Hours_Worked_TOTAL
FROM tb_user
INNER JOIN logging_system ON user_ID = login_user_id
GROUP BY user_name, date(login_date_time_entry);


Sample data (what is being generated):

Employee / Date_Log / Hours_Worked_TOTAL
================================================
John / 2005-09-05 / 7
Mary / 2005-09-05 / 7


Below, it is the data that I am looking for:

Employee / Date_Log / Hours_Worked_TOTAL
================================================
John / 2005-09-05 / 8:28:08
Mary / 2005-09-05 / 8:05:34


I don’t understand what is happening. Can somebody help me?

Thank you in advance.

E.W.