หาความต่างระหว่างสอง Datetime ใน MySQL ด้วย TIMESTAMPDIFF

ใช้ TIMESTAMPDIFF(unit, datetime1, datetime2) คำนวณความต่างในหน่วยที่ต้องการ

mysql> CREATE TABLE DemoTable (
    -> DueDatetime1 DATETIME,
    -> DueDatetime2 DATETIME
    -> );
Query OK, 0 rows affected (0.86 sec)

mysql> INSERT INTO DemoTable VALUES('2019-10-26 19:49:00', '2019-10-26 17:49:00');
Query OK, 1 row affected (0.19 sec)
mysql> INSERT INTO DemoTable VALUES('2019-10-26 08:00:00', '2019-10-26 13:00:00');
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO DemoTable VALUES('2019-10-26 06:50:00', '2019-10-26 12:50:00');
Query OK, 1 row affected (0.68 sec)

mysql> SELECT * FROM DemoTable;

+---------------------+---------------------+
| DueDatetime1        | DueDatetime2        |
+---------------------+---------------------+
| 2019-10-26 19:49:00 | 2019-10-26 17:49:00 |
| 2019-10-26 08:00:00 | 2019-10-26 13:00:00 |
| 2019-10-26 06:50:00 | 2019-10-26 12:50:00 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

หาความต่างเป็นนาที (ใช้ ABS ได้ค่าบวก):

mysql> SELECT ABS(TIMESTAMPDIFF(MINUTE, DueDatetime1, DueDatetime2)) FROM DemoTable;

+------------------------------------------------------+
| ABS(TIMESTAMPDIFF(MINUTE, DueDatetime1, DueDatetime2)) |
+------------------------------------------------------+
|                                                    120 |
|                                                    300 |
|                                                    360 |
+------------------------------------------------------+
3 rows in set (0.00 sec)

หน่วยที่รองรับ: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR