CODEWITHSUNDEEP
mysql
Newbieee
Newbieee

Reputation: 177

How to trigger calculate time difference between time_in and time_out in MySQL?

I created a Daily Time Record Table, I wanted to trigger calculate time difference between time_in - time_out added by over_time_in - over_time_out if the employee will work overtime.

How to trigger calculate the diff time in SQL on update value time_out or over_time_out?

if time_out != ''
   then trigger calculate (float) diffTime(time_in, time_out)

if over_time_out != ''
   then trigger calculate (float) diffTime(over_time_in, over_time_out)+diffTime(time_in, time_out)


| id | employee_id | employee_name | date       | time_in  | time_out | regular_time | over_time_in | over_time_out | total_work_hours |
|----|-------------|---------------|------------|----------|----------|--------------|--------------|---------------|------------------|
| 1  | 2021-100001 | John Doe      | 2021-08-03 | 08:35 AM | 06:51 PM | 11           | 07:05 PM     | 11:58 PM      | 13.69            |

sorry I am messing up with this because I dont know how to trigger in MySQL.

Upvotes: 0

Views: 402

Answers (1)

Akina
Akina

Reputation: 42728

Add generated column, like

ALTER TABLE DailyTimeRecordTable
ADD COLUMN time_difference FLOAT(2) AS (TIMESTAMPDIFF(SECOND, time_in, tme_out) / 3600);

The value will be calculated automatically.

Columns time_in and time_out must be TIME/DATETIME/TIMESTAMP. If not then use STR_TO_DATE() with proper pattern.

If time_out may be NULL / empty string / etc. then add according CASE.

Upvotes: 1

Related Questions