Detailed explanation of TIMESTAMPDIFF case in MySQL

1. Syntax

TIMESTAMPDIFF(unit,begin,end); Returns the time difference according to the unit. The data structures of the passed begin and end do not need to be the same. One can be Date and the other can be DateTime.

2. Unit

Supported units include:

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

3. Example

The following example is the most basic usage of TIMESTAMPDIFF.

• 3.1 How many months are there between 2017-01-01 - 2017-02-01

SELECT TIMESTAMPDIFF(MONTH, '2017-01-01', '2017-02-01') as result;

+--------+
| result |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)

• 3.2 How many days are there between 2017-01-01 - 2017-02-01

SELECT TIMESTAMPDIFF(DAY, '2017-01-01', '2017-02-01') as result;

+--------+
| result |
+--------+
| 31 |
+--------+
1 row in set (0.00 sec)

• 3.3 How many minutes are there between 2017-01-01 08: 00:00 - 2017-01-01 08: 55:00

SELECT TIMESTAMPDIFF(MINUTE, '2017-01-01 08:00:00', '2017-01-01 08:55:00') result;

+--------+
| result |
+--------+
| 55 |
+--------+
1 row in set (0.00 sec)

• 3.4 How many minutes are there between 2017-01-01 08: 00:00 - 2017-01-01 08: 55:33

SELECT TIMESTAMPDIFF(MINUTE, '2017-01-01 08:00:00', '2017-01-01 08:55:33') result;

+--------+
| result |
+--------+
| 55 |
+--------+
1 row in set (0.00 sec)

• 3.5 When calculating DIFF for DAY and MINUTE, the corresponding DAY and MINUTE will be subtracted directly.

• 3.6 How is SECOND calculated?

SELECT TIMESTAMPDIFF(SECOND, '2017-01-01 08:00:00', '2017-01-01 08:55:33') result;

55 * 60 + 33 = 3333
+--------+
| result |
+--------+
| 3333 |
+--------+
1 row in set (0.00 sec)

• 3.7 How to find the diff of two date fields in the database
  • 3.7.1 Create a table

    CREATE TABLE demo (id INT AUTO_INCREMENT PRIMARY KEY, start_time DATE NOT NULL, end_time DATE NOT NULL);
    Query OK, 0 rows affected (0.10 sec)

  • 3.7.2 Adding Data

    INSERT INTO demo(start_time, end_time)
    VALUES('1983-01-01', '1990-01-01'),
    ('1983-01-01', '1989-06-06'),
    ('1983-01-01', '1985-03-02'),
    ('1983-01-01', '1992-05-05'),
    ('1983-01-01 11:12:11', '1995-12-01');

  • 3.7.3 Directly query data

    select * from demo;
    +----+------------+------------+
    | id | start_time | end_time |
    +----+------------+------------+
    | 1 | 1983-01-01 | 1990-01-01 |
    | 2 | 1983-01-01 | 1989-06-06 |
    | 3 | 1983-01-01 | 1985-03-02 |
    | 4 | 1983-01-01 | 1992-05-05 |
    | 5 | 1983-01-01 | 1995-12-01 |
    +----+------------+------------+
    5 rows in set (0.00 sec)

  • 3.7.4 Calculating duration

    select *, TIMESTAMPDIFF(YEAR, start_time, end_time) as duration from demo;
    
    +----+------------+------------+----------+
    | id | start_time | end_time | duration |
    +----+------------+------------+----------+
    | 1 | 1983-01-01 | 1990-01-01 | 7 |
    | 2 | 1983-01-01 | 1989-06-06 | 6 |
    | 3 | 1983-01-01 | 1985-03-02 | 2 |
    | 4 | 1983-01-01 | 1992-05-05 | 9 |
    | 5 | 1983-01-01 | 1995-12-01 | 12 |
    +----+------------+------------+----------+
    5 rows in set (0.00 sec)

  • 3.7.5 Other Applications

    select *, if(TIMESTAMPDIFF(YEAR, end_time, CURRENT_TIMESTAMP())< 26 ,'< 26','>= 26') as result from demo;
    
    +----+------------+------------+--------+
    | id | start_time | end_time | result |
    +----+------------+------------+--------+
    | 1 | 1983-01-01 | 1990-01-01 | >= 26 |
    | 2 | 1983-01-01 | 1989-06-06 | >= 26 |
    | 3 | 1983-01-01 | 1985-03-02 | >= 26 |
    | 4 | 1983-01-01 | 1992-05-05 | < 26 |
    | 5 | 1983-01-01 | 1995-12-01 | < 26 |
    +----+------------+------------+--------+
    5 rows in set (0.00 sec)

This is the end of this article about the detailed explanation of TIMESTAMPDIFF case in MySQL. For more relevant content about TIMESTAMPDIFF in MySQL, please search the previous articles of 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!