How to find the time difference in mysql

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.

MySQL TIMEDIFF function introduction

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 set

In 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 set

MySQL TIMESTAMPDIFF function introduction

The 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:

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

##MySQL TIMESTAMPDIFF function example

The following example will return the difference between

2018-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

If you want to see Difference, just change the

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

The following statement returns two

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

Please note that

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

The difference should be 45 minutes and 59 seconds. However, we passed the

unit parameter as MINUTE, so the function returns 45 minutes as expected.

If

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;

Execute the above query statement and get the following results-

+----+---------------+---------------+-----+
| 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

In this statement, we calculate Age as of

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;

Execute the above query statement to get The following results-

+----+---------------+---------------+-----+
| 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

[Related recommendations:

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!