MySQL คำนวณ Time Difference ระหว่างสองคอลัมน์ DATETIME

ใช้ TIMEDIFF() และ TIME_FORMAT() คำนวณช่วงเวลา

TIMEDIFF(end, start) คืนค่า TIME (HH:MM:SS) ความต่างระหว่างสอง Datetime/Time

ตัวอย่าง: Punch In / Punch Out

CREATE TABLE DemoTable624 (
    PunchIn DATETIME,
    PunchOut DATETIME
);

INSERT INTO DemoTable624 VALUES
('2019-07-14 12:10:00', '2019-07-14 12:50:00'),
('2019-07-14 11:00:00', '2019-07-14 11:30:00');

Query: คำนวณระยะเวลา Format ชั่วโมง:นาที

SELECT 
    TIME_FORMAT(TIMEDIFF(PunchOut, PunchIn), '%H hrs, %i min.') AS duration
FROM DemoTable624;

ผลลัพธ์:

+-------------------+
| duration          |
+-------------------+
| 00 hrs, 40 min.   |
| 00 hrs, 30 min.   |
+-------------------+

⚠️ หมายเหตุ: ลำดับ Argument

TIMEDIFF(end, start) = end - start

บทความต้นฉบับใช้ TIMEDIFF(PunchIn, PunchOut) ได้ค่าลบ (-00 hrs, 40 min.)

Time Format Specifiers

Specifier	ความหมาย	ตัวอย่าง
%H	ชั่วโมง 00-23	00
%h/%I	ชั่วโมง 01-12	12
%i	นาที 00-59	40
%s	วินาที 00-59	00
%f	ไมโครวินาที 000000-999999	000000
%p	AM/PM	PM

คำนวณเป็น Minutes/Seconds ทั้งหมด

-- ทั้งหมดเป็นนาที
SELECT TIMESTAMPDIFF(MINUTE, PunchIn, PunchOut) AS minutes
FROM DemoTable624;
-- 40, 30

-- ทั้งหมดเป็นวินาที
SELECT TIMESTAMPDIFF(SECOND, PunchIn, PunchOut) AS seconds
FROM DemoTable624;
-- 2400, 1800

TIMESTAMPDIFF vs TIMEDIFF

Function	Return Type	Unit Control	ใช้เมื่อไหร่
TIMEDIFF	TIME (HH:MM:SS)	Fixed	Format แสดงผล
TIMESTAMPDIFF	INTEGER	YEAR/MONTH/DAY/HOUR/MINUTE/SECOND	คำนวณ/Filter/Group By

กรณีข้ามวัน (Overnight Shift)

INSERT INTO DemoTable624 VALUES
('2019-07-14 22:00:00', '2019-07-15 06:00:00');

-- TIMEDIFF ยังทำงานถูกต้อง (8 hours)
SELECT TIME_FORMAT(TIMEDIFF(PunchOut, PunchIn), '%H hrs, %i min.') 
FROM DemoTable624 WHERE PunchIn = '2019-07-14 22:00:00';
-- 08 hrs, 00 min.

Working Hours Calculation (Business Hours Only) - Advanced

-- ต้องใช้ Stored Procedure หรือ Application Logic
-- SQL มันยากมากสำหรับ Exclude Weekend/Holiday/Lunch Break