{"id":2862,"date":"2018-07-03T16:14:06","date_gmt":"2018-07-03T23:14:06","guid":{"rendered":"https:\/\/database.guide\/?p=2862"},"modified":"2021-01-28T14:10:36","modified_gmt":"2021-01-28T04:10:36","slug":"timestampdiff-examples-mysql","status":"publish","type":"post","link":"https:\/\/database.guide\/timestampdiff-examples-mysql\/","title":{"rendered":"TIMESTAMPDIFF() Examples – MySQL"},"content":{"rendered":"

The MySQL<\/a> TIMESTAMPDIFF()<\/code> function is used to find the difference between two date or datetime expressions. You need to pass in the two date\/datetime values, as well as the unit to use in determining the difference (e.g., day<\/em>, month<\/em>, etc). The TIMESTAMPDIFF()<\/code> function will then return the difference in the specified unit.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

First, here’s how the syntax goes:<\/p>\n

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)\r\n<\/pre>\n
Here, unit<\/code> is the unit to use in expressing the difference (e.g. day, month, year, etc). datetime_expr1<\/code> is the first date\/datetime value, and datetime_expr2<\/code> is the second.<\/p>\n
This function subtracts datetime_expr1<\/code> from datetime_expr2<\/code> and returns the result in unit<\/code>s. The result is returned as an integer.<\/p>\n
Valid Units<\/h3>\n
The unit<\/code> argument can be\u00a0any of the following:<\/p>\n
    \n
  • MICROSECOND<\/code><\/li>\n
  • \u00a0SECOND<\/code><\/li>\n
  • MINUTE<\/code><\/li>\n
  • HOUR<\/code><\/li>\n
  • DAY<\/code><\/li>\n
  • WEEK<\/code><\/li>\n
  • MONTH<\/code><\/li>\n
  • QUARTER<\/code><\/li>\n
  • YEAR<\/code><\/li>\n<\/ul>\n
    Example 1 – Difference in Days<\/h2>\n
    Here’s an example to demonstrate the basic usage of this function. Here we compare two date expressions and return the difference between them in days.<\/p>\n
    SELECT \r\n  TIMESTAMPDIFF(DAY,'2022-02-01','2022-02-21')\r\n  AS 'Difference in Days';\r\n<\/pre>\n
    Result:<\/p>\n
    +--------------------+\r\n| Difference in Days |\r\n+--------------------+\r\n|                 20 |\r\n+--------------------+\r\n<\/pre>\n
    Example 2 – Difference in Hours<\/h2>\n
    In this example we compare the same values as in the previous example, except here, we return the difference in hours<\/em>.<\/p>\n
    SELECT \r\n  TIMESTAMPDIFF(HOUR,'2022-02-01','2022-02-21')\r\n  AS 'Difference in Hours';\r\n<\/pre>\n
    Result:<\/p>\n
    +---------------------+\r\n| Difference in Hours |\r\n+---------------------+\r\n|                 480 |\r\n+---------------------+\r\n<\/pre>\n
    Example 3 – A ‘datetime’ Example<\/h2>\n
    Here’s an example that returns the difference in minutes. In this case, we compare two datetime values (as opposed to just the date values as in the previous examples).<\/p>\n
    SELECT \r\n  TIMESTAMPDIFF(MINUTE,'2022-02-01 10:30:27','2022-02-01 10:45:27')\r\n  AS 'Difference in Minutes';\r\n<\/pre>\n
    Result:<\/p>\n
    +-----------------------+\r\n| Difference in Minutes |\r\n+-----------------------+\r\n|                    15 |\r\n+-----------------------+\r\n<\/pre>\n
    Example 4 – Fractional Seconds<\/h2>\n
    You can go right down to the microsecond (6 digits) if you need to.<\/p>\n
    SELECT \r\n  TIMESTAMPDIFF(MICROSECOND,'2022-02-01 10:30:27.000000','2022-02-01 10:30:27.123456') \r\n  AS 'Difference in Microseconds';\r\n<\/pre>\n
    Result:<\/p>\n
    +----------------------------+\r\n| Difference in Microseconds |\r\n+----------------------------+\r\n|                     123456 |\r\n+----------------------------+\r\n<\/pre>\n
    Example 5 – Negative Results<\/h2>\n
    As would be expected, if the first date\/time argument is greater than the second, the result will be a negative integer.<\/p>\n
    SELECT \r\n  TIMESTAMPDIFF(DAY,'2022-02-21','2022-02-01')\r\n  AS 'Difference in Days';\r\n<\/pre>\n
    Result:<\/p>\n
    +--------------------+\r\n| Difference in Days |\r\n+--------------------+\r\n|                -20 |\r\n+--------------------+\r\n<\/pre>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    The MySQL TIMESTAMPDIFF() function is used to find the difference between two date or datetime expressions. You need to pass in the two date\/datetime values, as well as the unit to use in determining the difference (e.g., day, month, etc). The TIMESTAMPDIFF() function will then return the difference in the specified unit.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[68,115,93],"class_list":["post-2862","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-date-functions","tag-dates","tag-functions"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2862","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=2862"}],"version-history":[{"count":8,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2862\/revisions"}],"predecessor-version":[{"id":2870,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2862\/revisions\/2870"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2862"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2862"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2862"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}