How to use the MySQL TIMESTAMPDIFF() function

In this article, we will learn how to use the MySQL TIMESTAMPDIFF() function, which returns the difference between two timestamp values, in a specified unit.

Posted on

In this article, we will learn how to use the MySQL TIMESTAMPDIFF() function, which returns the difference between two timestamp values, in a specified unit. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with timestamps and differences.

Syntax

The syntax of the TIMESTAMPDIFF() function is as follows:

TIMESTAMPDIFF(unit, timestamp1, timestamp2)

The unit parameter can be any of the date and time units that are supported by MySQL, such as SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR, etc. The timestamp1 and timestamp2 parameters can be any valid timestamp expressions, or strings that can be converted to timestamp values. If any parameter is NULL, the function returns NULL. The TIMESTAMPDIFF() function returns an integer value that represents the difference between the two given timestamp values, in the specified unit. For example, TIMESTAMPDIFF(SECOND, '2023-01-15 10:02:34', '2023-01-15 10:03:34') returns 60, which is the number of seconds between the two timestamp values.

Examples

Let’s see some examples of how to use the TIMESTAMPDIFF() function in MySQL.

Example 1: Get the difference between two timestamp values in seconds

We can use the TIMESTAMPDIFF() function to get the difference between two timestamp values in seconds. For example:

SELECT TIMESTAMPDIFF(SECOND, '2023-01-15 10:02:34', '2023-01-15 10:03:34') AS result;

This query will return the difference between the two timestamp values ‘2023-01-15 10:02:34’ and ‘2023-01-15 10:03:34’ in seconds. The query will return 60, which is the number of seconds between the two timestamp values.

Example 2: Get the difference between two timestamp values in minutes

We can use the TIMESTAMPDIFF() function to get the difference between two timestamp values in minutes. For example:

SELECT TIMESTAMPDIFF(MINUTE, '2023-01-15 10:02:34', '2023-01-15 10:03:34') AS result;

This query will return the difference between the two timestamp values ‘2023-01-15 10:02:34’ and ‘2023-01-15 10:03:34’ in minutes. The query will return 1, which is the number of minutes between the two timestamp values.

Example 3: Get the difference between two timestamp values in hours

We can use the TIMESTAMPDIFF() function to get the difference between two timestamp values in hours. For example:

SELECT TIMESTAMPDIFF(HOUR, '2023-01-15 10:02:34', '2023-01-15 11:02:34') AS result;

This query will return the difference between the two timestamp values ‘2023-01-15 10:02:34’ and ‘2023-01-15 11:02:34’ in hours. The query will return 1, which is the number of hours between the two timestamp values.

Example 4: Get the difference between two timestamp values in days

We can use the TIMESTAMPDIFF() function to get the difference between two timestamp values in days. For example:

SELECT TIMESTAMPDIFF(DAY, '2023-01-15 10:02:34', '2023-01-16 10:02:34') AS result;

This query will return the difference between the two timestamp values ‘2023-01-15 10:02:34’ and ‘2023-01-16 10:02:34’ in days. The query will return 1, which is the number of days between the two timestamp values.

Example 5: Get the difference between two timestamp values in months

We can use the TIMESTAMPDIFF() function to get the difference between two timestamp values in months. For example:

SELECT TIMESTAMPDIFF(MONTH, '2023-01-15 10:02:34', '2023-02-15 10:02:34') AS result;

This query will return the difference between the two timestamp values ‘2023-01-15 10:02:34’ and ‘2023-02-15 10:02:34’ in months. The query will return 1, which is the number of months between the two timestamp values.

There are some other functions that are related to the TIMESTAMPDIFF() function, and can be useful for working with timestamps and differences. Here are some of them:

  • TIME(): This function returns the time part of a date or datetime value, or a string that can be converted to a time value. For example, TIME('2023-01-15 10:02:34') returns ‘10:02:34’.
  • DATE(): This function returns the date part of a date or datetime value, or a string that can be converted to a date value. For example, DATE('2023-01-15 10:02:34') returns ‘2023-01-15’.
  • DATETIME(): This function returns the datetime value of a date or datetime value, or a string that can be converted to a datetime value. For example, DATETIME('2023-01-15 10:02:34') returns ‘2023-01-15 10:02:34’.
  • TIMESTAMP(): This function returns the timestamp value of a date, datetime, or timestamp value, or a string that can be converted to a timestamp value. For example, TIMESTAMP('2023-01-15 10:02:34') returns ‘2023-01-15 10:02:34’.
  • TIMESTAMPADD(): This function returns the timestamp value that is the result of adding a specified interval to a timestamp value. For example, TIMESTAMPADD(MINUTE, 1, '2023-01-15 10:02:34') returns ‘2023-01-15 10:03:34’, which is the timestamp value after adding one minute.

Conclusion

In this article, we learned how to use the MySQL TIMESTAMPDIFF() function, which returns the difference between two timestamp values, in a specified unit. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with timestamps and differences.