在phpMyAdmin中设置Virtual列计算员工工作时长的表达式

Hey there! Let's get that VIRTUAL total_hours column set up correctly in phpMyAdmin.

First, the exact expression you'll need to use in the Generated Column field (phpMyAdmin uses this term for virtual/generated columns) is:

TIME_TO_SEC(DATE_SUB(TIMEDIFF(finish_time, start_time), INTERVAL `break` MINUTE)) / 3600

A quick important note: I wrapped break in backticks because it's a reserved keyword in MySQL—this prevents syntax errors that might pop up otherwise.

Let’s verify this with your example to confirm it works as expected:

• When start_time = 5:00, finish_time = 13:00, break = 30
  1. TIMEDIFF(13:00, 5:00) returns 08:00:00 (8 full hours)
  2. DATE_SUB(..., INTERVAL 30 MINUTE) subtracts the break time, resulting in 07:30:00
  3. TIME_TO_SEC() converts that time to 27000 seconds
  4. Dividing by 3600 gives 7.5, which matches your desired result.

Here’s the step-by-step to apply this in phpMyAdmin:

• Navigate to your table’s Structure page
• Click the Add column button
• Name the column total_hours
• Set the Type to DECIMAL (I recommend DECIMAL(5,2) to support values up to 999.99 hours)
• From the Generated dropdown, select VIRTUAL
• Paste the expression above into the Expression field
• Save your changes, and the column will automatically calculate the actual work hours whenever related fields are updated.

内容的提问来源于stack exchange，提问作者Healyhatman