Best way to store attendance and get difference of time in MySQL?

I’m very new to the SQL language and MySQL. I’m working on a little Time and Attendance application IN PYTHON. What it does is logs the clock in and out time. Then calculates the hours worked and returns the amount according to the time worked.

What I want to know is the best way to store these clock in and clock out times and how to get the time difference?

So far, I’ve made a table for the employees as follows:

CREATE TABLE employees (employee_id INT(50) AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(20), 
last_name VARCHAR(25), 
age INT(80), 
mobile_number VARCHAR(16), 
national_id VARCHAR(15), 
guardian_national_id VARCHAR(15), 
email_id VARCHAR(150), 
password VARCHAR(255), 
join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Here the join_date is the creation of this employee and the rest I’m guessing is self explanatory.

I 2 things in mind. It was to either make two separate tables, one for clocking in and one for clocking out. Then getting calculating and returning the time worked in Python. For this, I don’t know how to get the verify the employee_id in these tables. Do I use the INNER JOIN or the FOREIGN KEY.

or, I could have only one table that will have the clock in and clock out columns but I still don’t know how to verify the employee_id.

I know that the start_time would be TIMESTAMP DEFAULT CURRENT_TIMESTAMP but when I goto update the clock out time, wouldn’t this be updated again?

I am lost. Please help me, this is very painful. I can’t find my answers anywhere. Thanks,

Answer

The way forward here is to create a separate table to store the start and finish times in. You can store them either as integers (and use Unix timestamps), or, better still, store them as MySQL datetime types – this way you can use function in MySQL, such as TIMESTAMPDIFF.

CREATE TABLE shifts (
  employee_id INT(50),
  start datetime NOT NULL, 
  finish datetime NULL,
  duration int(11) GENERATED AS (TIMESTAMPDIFF(MINUTE, start, end)) STORED NULL,
  PRIMARY KEY(employee_id, start)
);

Then, link each row to the appropriate employee using a foreign key reference to the employee_id column on the employees table.

CREATE INDEX employee ON shifts(employee_id);

ALTER TABLE shifts
ADD FOREIGN KEY fk_employee_shifts(employee_id)
REFERENCES employees(employee_id)
ON DELETE CASCADE
ON UPDATE CASCADE

The above is probably pseudo code as I’m a beer into my evening 🙂

I’ve made the table a bit smarter by using a stored virtual column to hold the TIMESTAMPDIFF value. This way you won’t need to pull data into your application to calculate the length of each session; let the database do the work! Just watch out for timezones…