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 twoTIMEorDATETIMEvalues difference. See the following syntax for theTIMEDIFFfunction.

TIMEDIFF(dt1, dt2);

TIMEDIFFThe function accepts two parameters that must be of the same type, namelyTIMEorDATETIME. TheTIMEDIFFfunction returns the result ofdt1 - dt2expressed as a time value.

Because theTIMEDIFFfunction returns aTIMEvalue, its result is limited to the range from-838:59:59to838: 59:59is within the value range ofTIME.

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 between12:00:00and10:00:00as:02: 00:00.

The following example calculates the difference between twoDATETIMEvalues:

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 isNULL,TIMEDIFFThe function will returnNULL.

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 isDATETIMEand the other isTIME, theTIMEDIFFfunction also returnsNULL.

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 theTIMESTAMPDIFFfunction.

TIMESTAMPDIFF(unit,begin,end);

TIMESTAMPDIFFfunction returns the result ofbegin-end, wherebeginandendareDATEorDATETIMEexpression. The

TIMESTAMPDIFFfunction allows its arguments to have mixed types, for example,beginis aDATEvalue andendcan beDATETIMEvalue. If aDATEvalue is used, theTIMESTAMPDIFFfunction treats it as aDATETIMEvalue with the time part"00:00:00". The

unitparameter 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

2018-01-01and2018-06-01as a month value:

mysql> SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-06-01') result; +--------+ | result | +--------+ | 5 | +--------+ 1 row in set

unitparameter fromMONTHtoDAY, as shown below:

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

DATETIMEDifference 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

TIMESTAMPDIFFonly considers the portion of time associated with theunitparameter. 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

unitparameter asMINUTE, so the function returns 45 minutes as expected.

SECONDis used instead ofMINUTE, theTIMESTAMPDIFFfunction will consider theSECONDpart, 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 namedpersonsfor 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 thepersonstable:

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, useTIMESTAMPDIFFto calculate the age of each person in thepersonstable:

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 theNOWfunction, 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!