TIMESTAMPDIFF() Function Examples – MySQL

TIMESTAMPDIFF() Function Examples – MySQL

In this MySQL tutorial, we would love to share with you how to use this timestampdiff() function. How it works?

MySQL TIMESTAMPDIFF() Function

The MySQL TIMESTAMPDIFF () function is used to find the difference between two date or DateTime expressions.

Note: You need to pass two date / datetime values ​​along with the unit (eg, day, month, etc.) to use for determining the difference. The TIMESTAMPDIFF () function will then return the difference in the unit specified.

Syntax

The syntax is MySQL timestampdiff() is:

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

This function reduces datetime_expr1 from datetime_expr2 and returns results in units. The result is returned as an integer.

Valid Units

The unit an argument can be any of the following:

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

Example-1

Here’s an example, we will display the usage of this function. Here we compare the two dates and return the differences between them in days.

  SELECT 
  TIMESTAMPDIFF(DAY,'2022-02-01','2022-02-21')
  AS 'Difference in Days';

Output-1

+--------------------+
| Difference in Days |
+--------------------+
|                 20 |
+--------------------+

Example-2

In this example, we compare the values ​​of the previous examples, except by leaving here, we return the difference in hours.

  SELECT 
  TIMESTAMPDIFF(HOUR,'2022-02-01','2022-02-21')
  AS 'Difference in Hours';

Output-2

+---------------------+
| Difference in Hours |
+---------------------+
|                 480 |
+---------------------+

Example-3

Here’s an example that returns a difference in minutes. In this case, we compare two data-time values.

  SELECT 
  TIMESTAMPDIFF(MINUTE,'2022-02-01 10:30:27','2022-02-01 10:45:27')
  AS 'Difference in Minutes';

Output-3

+-----------------------+
| Difference in Minutes |
+-----------------------+
|                    15 |
+-----------------------+

Example-4

As expected, if the first date/time argument is greater than the second, the result would be a negative integer.

  SELECT 
  TIMESTAMPDIFF(DAY,'2022-02-21','2022-02-01')
  AS 'Difference in Days';

Output-4

+--------------------+
| Difference in Days |
+--------------------+
|                -20 |
+--------------------+

Example-5

In this statement, We take database table example, here we calculated the ages. See the below example:

 SELECT id, name, date_of_birth, TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) age
 FROM
 employees; 

Conclusion

Here, you have learned how to use MySQL TIMESTAMPDIFF() function with various examples.

You may like

  1. TIMESTAMP() Function Examples – MySQL
  2. TIMEDIFF() Function Examples – MySQL
  3. SEC_TO_TIME() Function Examples – MySQL
  4. QUARTER() Function Examples – MySQL
  5. STR_TO_DATE() Function Examples – MySQL
  6. PERIOD_DIFF() Function Examples – MySQL
  7. PERIOD_ADD() Function Examples – MySQL
  8. SECOND() Function Examples – MySQL
  9. MICROSECOND() Function Examples – MySQL
  10. MAKETIME() Function Examples – MySQL
  11. Localtime() Function Examples – MySQL
  12. CURTIME() Function Examples – MySQL
  13. MySQL CURRENT TIMESTAMP Function Examples
  14. MySQL CURRENT TIME Function Examples
  15. ADDTIME() Function Examples – MySQL
  16. MINUTE() Function Examples – MySQL
  17. HOUR() Function Examples – MySQL
  18. NOW() MySQL Function | MySQL
  19. MySQL DATE() And TIME() Functions (List)
  20. TIMESTAMPADD() Function Examples – MySQL

If you have any questions or thoughts to share, use the comment form below to reach us.

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Javascript, JQuery, Laravel, Livewire, Codeigniter, Vue JS, Angular JS, React Js, WordPress, and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *