MySQL Timestampdiff

By /
MySQL Timestampdiff

In this tutorial, we will learn how to implement the MySQL TIMESTAMPDIFF function which will calculate the difference between two DATE or DATETIME values.

Also read: MySQL Timestamp – Definition and Usage

Introduction

TIMESTAMPDIFF() in MySQL returns a value after dividing one DateTime expression by another.
The two expressions don’t have to be of the same type. Date or DateTime could be one of them. Date values are interpreted as DateTime with the time part set to 00:00:00 by default. Another argument provides the unit for the result.

SYNTAX

TIMESTAMPDIFF(unit,expr1,expr2);

  1. The unit argument specifies how the integer representation of the result of (expr2 – expr1) will be expressed.
  2. The result of (expr1 – expr2), where expr1 and expr2 are DATE or DATETIME expressions, is what the TIMESTAMPDIFF function returns.

Examples 1 – Calculating the difference between months:

Let us calculate the difference between months of a year.

SELECT TIMESTAMPDIFF(MONTH, '2022-05-11', '2022-09-15') RESULT;Code language: JavaScript (javascript)
Timestampdiff 1
Timestampdiff 1

Example 2 – Calculating the difference between days:

To calculate the difference between days, just change the argument MONTH to DAY.

SELECT TIMESTAMPDIFF(DAY, '2022-05-11', '2022-09-15') RESULT;Code language: JavaScript (javascript)
Timestampdiff 2
Timestampdiff 2

Example 3 – Calculating the difference in minutes of DateTime values:

Here, we will calculate the difference in minutes of two DateTime values.

SELECT TIMESTAMPDIFF(MINUTE, '2022-05-11 10:45:27', '2022-05-11 11:55:30') RESULT;Code language: JavaScript (javascript)
Timestampdiff 3
Timestampdiff 3

Calculating the difference in seconds of DateTime values:

To calculate the difference, just change the argument MINUTE to SECOND.

SELECT TIMESTAMPDIFF(MINUTE, '2022-05-11 10:45:27', '2022-05-11 11:55:30') RESULT;Code language: JavaScript (javascript)
Timestampdiff 4
Timestampdiff 4

Calculating ages:

Here we will calculate the age of a person using the TIMESTAMPDIFF function. First of all create a table named DETAIL and insert values into it.

CREATE TABLE DETAIL(
Id INT,
Name VARCHAR(300),
DOB DATE
);
INSERT INTO DETAIL (Id, Name, DOB)
VALUES(1, 'john', '1990-02-09'),
(2, 'peter', '1994-01-01'),
(3, 'david', '1986-05-07'),
(4, 'William', '1995-08-05'),
(5, 'lily', '1997-06-11');Code language: JavaScript (javascript)

Now using the TIMESTAMPDIFF function we will calculate the ages of each person from the DETAIL table.

SELECT Id, Name, DOB,
TIMESTAMPDIFF(YEAR, DOB, '2022-01-01') Age
FROM DETAIL;Code language: JavaScript (javascript)
Timestamp 5
Timestamp 5

The ages of each person have been calculated here and added to another column right beside it.

Summary

In this tutorial, we studied how to implement the TIMESTAMPDIFF function. For more reference, check the official documentation of MySQL.