How to use MySQL DATEDIFF function to get the time interval between two dates

describe

Returns the time interval between two dates.

grammar

DateDiff(interval, date1, date2 [ ,firstdayofweek[ , firstweekofyear]] )

The DateDiff function syntax has these arguments:

set up

The interval parameter can have the following values:

The firstdayofweek parameter can have the following values:

The firstweekofyear parameter can have the following values:

illustrate

The DateDiff function determines the number of specified time intervals that exist between two dates. For example, you can use DateDiff to calculate the number of days between two dates, or the number of weeks between the current day and the last day of the year.

To calculate the number of days between date1 and date2, you can use the "day of year" ("y") or the "day" ("d"). When interval is "day of week" ("w"), DateDiff returns the week number between two dates. If date1 is a Monday, DateDiff calculates the number of Mondays before date2. This result includes date2 but not date1. If interval is "week" ("ww"), the DateDiff function returns the number of weeks between two dates in the calendar table. The function calculates the number of Sundays between date1 and date2. DateDiff will evaluate date2 if date2 is a Sunday, but will not evaluate date1 even if date1 is a Sunday.

If date1 is later than date2, the DateDiff function returns a negative number.

The firstdayofweek parameter affects calculations using the "w" and "ww" interval symbols.

If date1 or date2 is a date literal, the specified year becomes a fixed part of the date. However, if date1 or date2 is enclosed in quotation marks (" ") and the year is omitted, the current year is inserted each time the date1 or date2 expression is evaluated in the code. This allows you to write program code that works for different years.

When interval is "year" ("yyyy"), comparing December 31st and January 1st of the following year, although they are actually only one day apart, DateDiff returns 1 to indicate a difference of one year.

In MySQL, DATEDIFF(date1, date2) returns the number of days between the start time date1 and the end time date2. date1 and date2 are dates or date-and-time expressions. Only the date portion of these values ​​is used in calculations.

[Example] Use the DATEDIFF() function to calculate the number of days between two dates. The input SQL statement and execution result are shown below.

mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,
  -> DATEDIFF('2017-11-30','2017-12-15') AS col2;
+------+------+
| col1 | col2 |
+------+------+
| 1 | -15 |
+------+------+
1 row in set (0.00 sec)

From the running results, we can see that the DATEDIFF() function returns the value after date1-date2, so the return value of DATEDIFF('2017-11-30', '2017-11-29') is 1, and the return value of DATEDIFF('2017-11-30 23:59:59', '2017-12-15') is -15.

This is the end of this article about how to use the MySQL DATEDIFF function to get the time interval between two dates. For more information about MySQL DATEDIFF date time interval, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!