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

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:

parameter describe
interval Required. A string expression representing the time interval to be calculated between date1 and date2. See the Settings section for values.
date1, date2 Required. Date expression. The two dates used in the calculation.
firstdayofweek Optional. A constant specifying the first day of the week. If not specified, defaults to Sunday. See the Settings section for values.
firstweekofyear Optional. A constant specifying the first week of the year. If not specified, defaults to the weekday containing January 1. See the Settings section for values.

set up

The interval parameter can have the following values:

set up describe
yyyy Year
q Quarter
m moon
y Number of days in a year
d day
w Days of the week
ww week
h Hour
m minute
s Second

The firstdayofweek parameter can have the following values:

constant value describe
vbUseSystem 0 Use the National Language Support (NLS) API settings.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday

The firstweekofyear parameter can have the following values:

constant value describe
vbUseSystem 0 Use the National Language Support (NLS) API settings.
vbFirstJan1 1 Start with the week ending January 1 (default).
vbFirstFourDays 2 Begins with the first week that has at least four days in the new year.
vbFirstFullWeek 3 Begins with the first full week of the new year.

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!

You may also be interested in:
  • MySql query time period method
  • How to query date and time in mysql
  • MySQL query statement grouped by time
  • MySql database time series interval query method

<<:  A graphic tutorial on how to install redhat 8.0 system (a must-have for beginners)

>>:  Demystifying the HTML 5 Working Draft

Recommend

Detailed explanation of Nginx access restriction configuration

What is Nginx access restriction configuration Ng...

Detailed explanation of Docker container network port configuration process

Exposing network ports In fact, there are two par...

5 Ways to Send Emails in Linux Command Line (Recommended)

When you need to create an email in a shell scrip...

XHTML Web Page Tutorial

<br />This article is mainly to let beginner...

Detailed explanation of the usage and difference between nohup and & in Linux

Example: We use the Python code loop_hello.py as ...

MySQL enables slow query (introduction to using EXPLAIN SQL statement)

Today, database operations are increasingly becom...

About the problem of vertical centering of img and span in div

As shown below: XML/HTML CodeCopy content to clip...

Detailed tutorial on distributed operation of jmeter in docker environment

1. Build the basic image of jmeter The Dockerfile...

In-depth understanding of Vue-cli4 routing configuration

Table of contents Preface - Vue Routing 1. The mo...

...

CSS HACK for IE6/IE7/IE8/IE9/FF (summary)

Since I installed the official version of IE8.0, ...

Basic tutorial on using explain statement in MySQL

Table of contents 1. Overview 1. Explain statemen...