Mysql method to find the time difference: 1. Use the TIMEDIFF() function, the syntax "TIMEDIFF(dt1, dt2);"; 2. Use the IMESTAMPDIFF() function, the syntax "TIMESTAMPDIFF(unit,begin,end); ".
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
mysql finds the time difference
timediff() function - calculates the difference between two TIME or DATETIME values .
timestampdiff() function - Calculates the difference between two DATE or DATETIME values.
TIMEDIFFReturns between two TIME or DATETIME values difference. See the following syntax for the TIMEDIFF function.
TIMEDIFF(dt1, dt2);
TIMEDIFF The function accepts two parameters that must be of the same type, namely TIME or DATETIME. The TIMEDIFF function returns the result of dt1 - dt2 expressed as a time value.
Because the TIMEDIFF function returns a TIME value, its result is limited to the range from -838:59:59 to 838: 59:59 is within the value range of TIME.
MySQL TIMEDIFF Function Example
Let us take an example to calculate the difference between two time values.
mysql> SELECT TIMEDIFF('12:00:00','10:00:00') diff;
+----------+
| diff |
+----------+
| 02:00:00 |
+----------+
1 row in setIn this example, we calculated the difference between 12:00:00 and 10:00:00 as: 02: 00:00.
The following example calculates the difference between two DATETIME values:
mysql> SELECT TIMEDIFF('2010-01-01 01:00:00', '2010-01-02 01:00:00') diff;
+-----------+
| diff |
+-----------+
| -24:00:00 |
+-----------+
1 row in set If either argument is NULL, TIMEDIFFThe function will return NULL.
mysql> SELECT TIMEDIFF('2010-01-01',NULL) diff;
+------+
| diff |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)If you pass two parameters of different types, one is DATETIME and the other is TIME, the TIMEDIFF function also returns NULL.
mysql> SELECT TIMEDIFF('2010-01-01 10:00:00','10:00:00') diff;
+------+
| diff |
+------+
| NULL |
+------+
1 row in setThe following explains the syntax of the TIMESTAMPDIFF function.
TIMESTAMPDIFF(unit,begin,end);
TIMESTAMPDIFF function returns the result of begin-end, where begin and end are DATE or DATETIME expression. The
TIMESTAMPDIFF function allows its arguments to have mixed types, for example, begin is a DATE value and end can be DATETIME value. If a DATE value is used, the TIMESTAMPDIFF function treats it as a DATETIME value with the time part "00:00:00". The
unit parameter is the unit used to determine the result of (end-begin), expressed as an integer. The following are valid units:
##MySQL TIMESTAMPDIFF function example
The following example will return the difference between2018-01-01 and 2018-06-01 as a month value:
mysql> SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-06-01') result; +--------+ | result | +--------+ | 5 | +--------+ 1 row in set
unit parameter from MONTH to DAY, as shown below:
mysql> SELECT TIMESTAMPDIFF(DAY, '2010-01-01', '2010-06-01') result; +--------+ | result | +--------+ | 151 | +--------+ 1 row in set
DATETIME Difference value in minutes:
mysql> SELECT TIMESTAMPDIFF(MINUTE, '2018-01-01 10:00:00', '2018-01-01 10:45:00') result; +--------+ | result | +--------+ | 45 | +--------+ 1 row in set
TIMESTAMPDIFF only considers the portion of time associated with the unit parameter. See the example below:
mysql> SELECT TIMESTAMPDIFF(MINUTE, '2018-01-01 10:00:00', '2018-01-01 10:45:59') result; +--------+ | result | +--------+ | 45 | +--------+ 1 row in set
unit parameter as MINUTE, so the function returns 45 minutes as expected.
SECOND is used instead of MINUTE, the TIMESTAMPDIFF function will consider the SECOND part, as shown in the following example :
mysql> SELECT TIMESTAMPDIFF(SECOND, '2018-01-01 10:00:00', '2018-01-01 10:45:59') result; +--------+ | result | +--------+ | 2759 | +--------+ 1 row in set
Note: 45 minutes and 59 seconds = 45×60 59 (seconds) = 2759 seconds
Use the MySQL TIMESTAMPDIFF function to calculate age
First , we create a new table named persons for demonstration purposes.
USE testdb; CREATE TABLE persons ( id INT AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(255) NOT NULL, date_of_birth DATE NOT NULL );
Second, insert some rows into the persons table:
INSERT INTO persons(full_name, date_of_birth)
VALUES('John Doe', '1990-01-01'),
('David Taylor', '1989-06-06'),
('Peter Drucker', '1985-03-02'),
('Lily Minsu', '1992-05-05'),
('Mary William', '1995-12-01');The third step, use TIMESTAMPDIFF to calculate the age of each person in the persons table:
SELECT id, full_name, date_of_birth, TIMESTAMPDIFF(YEAR, date_of_birth, '2018-01-01') age FROM persons;
+----+---------------+---------------+-----+ | id | full_name | date_of_birth | age | +----+---------------+---------------+-----+ | 1 | John Doe | 1990-01-01 | 28 | | 2 | David Taylor | 1989-06-06 | 28 | | 3 | Peter Drucker | 1985-03-02 | 32 | | 4 | Lily Minsu | 1992-05-05 | 25 | | 5 | Mary William | 1995-12-01 | 22 | +----+---------------+---------------+-----+ 5 rows in set
2018-01-01. If you want to calculate the current age, you can replace the literal value '2018-01-01' with the NOW function, as shown below:
SELECT id, full_name, date_of_birth, TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) age FROM persons;
+----+---------------+---------------+-----+ | id | full_name | date_of_birth | age | +----+---------------+---------------+-----+ | 1 | John Doe | 1990-01-01 | 27 | | 2 | David Taylor | 1989-06-06 | 28 | | 3 | Peter Drucker | 1985-03-02 | 32 | | 4 | Lily Minsu | 1992-05-05 | 25 | | 5 | Mary William | 1995-12-01 | 21 | +----+---------------+---------------+-----+ 5 rows in set
mysql video tutorial]
The above is the detailed content of How to find the time difference in mysql. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
