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

Solution to the problem "Table mysql.plugin doesn't exist" when deploying MySQL

Today I deployed the free-installation version of...

MYSQL database GTID realizes master-slave replication (super convenient)

1. Add Maria source vi /etc/yum.repos.d/MariaDB.r...

Two ways to prohibit clearing the input text input cache in html

Most browsers will cache input values ​​by defaul...

40 web page designs with super large fonts

Today's web designs tend to display very larg...

Implementing a simple web clock with JavaScript

Use JavaScript to implement a web page clock. The...

Example of Vue routing listening to dynamically load the same page

Table of contents Scenario Analysis Development S...

Time zone issues with Django deployed in Docker container

Table of contents Time zone configuration in Djan...

Solution to MySql Error 1698 (28000)

1. Problem description: MysqlERROR1698 (28000) so...

JavaScript to achieve product query function

This article example shares the specific code of ...

Linux Disk Quota Management Graphical Example

Disk quota is the storage limit of a specified di...

Web Design Summary

<br />From the birth of my first personal pa...