For my school project I need to make a clocking system for employees in jsp. I management to become a clocking card for each employee. This data comes from a date/time field in the database.
But now I want to know how many minutes each employee has worked each day. I have searched for some sql functions to do this, but I don't think there is any proper function that is capable of managing what I ask for.
What I want to become is that an employee can clock 4 times each day. And that there is an overview of how many minutes each employee worked each day.
In the attachment you can find the clocking card that I already have.
Build a table (let's call it EmplyeeReports) in your database with three columns: employee_id, date_time, minutes. Every time an employee clocks in (you said 4 times a day) and enters the number of minutes they worked since the last time they clocked in, you insert a row in that table with their employee id, the exact date and time of when they are clocking in and the minutes they are reporting.
Once you have the data, here is the query (for MySQL) to retrieve the number of minutes for each employee for each day:
select employee_id, date(date_time), sum(minutes) from EmployeeReports group by employee_id, date(date_time);
Please enjoy this holographic presentation of our apocalyptic dilemma right after this tiny ad: