Shulou(Shulou.com)05/31 Report--

This article mainly introduces mysql how to find the time difference, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Mysql to find the time difference method: 1, use TIMEDIFF () function, syntax "TIMEDIFF (dt1, dt2);"; 2, use IMESTAMPDIFF () function, syntax "TIMESTAMPDIFF (unit,begin,end);".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Mysql calculates 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.

Introduction to MySQL TIMEDIFF function

TIMEDIFF returns the difference between two TIME or data values. See the following syntax for the TIMEDIFF function.

TIMEDIFF (dt1, dt2)

The TIMEDIFF function accepts two parameters that must be of the same type, TIME or DATETIME. The TIMEDIFF function returns the result of dt1-dt2 represented as a time value.

Because the TIMEDIFF function returns a time value, the result is limited to a range of time values from-838 to 838.

Example of MySQL TIMEDIFF function

Let's give an example to calculate the difference between two time values.

Mysql > SELECT TIMEDIFF 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-01-01-01-01-01-01-01-01-02-01-01) diff;+-+ | diff | +-+ |-24:00:00 | +-+ 1 row in set

If any argument is a NULL,TIMEDIFF function, it returns NULL.

Mysql > SELECT TIMEDIFF ('0-01-01-01-01-01-01) diff;+-+ | diff | +-+ | NULL | +-+ 1 row in set, 1 warning (2010 sec)

If you pass two different types of parameters, one is DATETIME, and the other is that the TIME,TIMEDIFF function also returns NULL.

Mysql > SELECT TIMEDIFF ('2010-01-01 10 diff;+-+ 0012) diff;+-+ | diff | +-+ | NULL | introduction to +-+ 1 row in setMySQL TIMESTAMPDIFF function

The syntax of the TIMESTAMPDIFF function is explained below.

TIMESTAMPDIFF (unit,begin,end)

The TIMESTAMPDIFF function returns the result of begin-end, where begin and end are DATE or DATETIME expressions.

The TIMESTAMPDIFF function allows its parameters to have mixed types, for example, begin is a date value, and end can be a date value. If you use a date value, the TIMESTAMPDIFF function treats it as a date value with a time portion of "00:00:00".

The unit parameter is the unit of the result of the end-begin, expressed as an integer. The following are valid units:

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

Example of MySQL TIMESTAMPDIFF function

The following example returns 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 the difference, simply change the unit parameter from MONTH to DAY, as follows:

Mysql > SELECT TIMESTAMPDIFF (DAY, '2010-01-01,' 2010-06-01') result;+-+ | result | +-+ | 151 | +-+ 1 row in set

The following statement returns the difference between two DATETIME values in minutes:

Mysql > SELECT TIMESTAMPDIFF (MINUTE, '2018-01-01 10 MINUTE,' 2018-01-01 10 MINUTE 45) result;+-+ | result | +-+ | 45 | +-+ 1 row in set

Note that TIMESTAMPDIFF only considers the time portion related to the unit parameter. See the following example:

Mysql > SELECT TIMESTAMPDIFF (MINUTE, '2018-01-01 10 MINUTE,' 2018-01-01 10 MINUTE 45) result;+-+ | result | +-+ | 45 | +-+ 1 row in set

The difference should be 45 minutes and 59 seconds. However, we passed the unit argument to MINUTE, so the function returned 45 minutes as expected.

If you use SECOND instead of MINUTE, the TIMESTAMPDIFF function considers the SECOND section, as shown in the following example:

Mysql > SELECT TIMESTAMPDIFF (SECOND, '2018-01-01 10 SECOND,' 2018-01-01 10 SECOND 45 SECOND 59') result;+-+ | result | +-+ | 2759 | +-+ 1 row in set

Note: 45 minutes 59 seconds = 45 × 60 + 59 (seconds) = 2759 seconds

Use the MySQL TIMESTAMPDIFF function to calculate age

First, we create a new table called persons for demonstration.

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 is to use TIMESTAMPDIFF to calculate the age of everyone in the persons table:

SELECT id, full_name, date_of_birth, TIMESTAMPDIFF (YEAR, date_of_birth, '2018-01-01') ageFROM persons

Execute the above query 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 the age up to 2018-01-01. If you want to calculate the current age, you can replace the literal value '2018-01-01-01 by using the NOW function, as shown below:

SELECT id, full_name, date_of_birth, TIMESTAMPDIFF (YEAR, date_of_birth, NOW ()) ageFROM persons

Execute the above query and 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 Thank you for reading this article carefully | I hope the article "how to find the time difference in mysql" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.