MySQL Date and Time Functions
MySQL ADDDATE() Function
The ADDDATE()
method in MySQL outputs a date after adding a date/time period to a date or datetime value.
Syntax:
The syntax for the MySQL ADDDATE() function is as follows:
ADDDATE(date, days)
or
ADDDATE(date, INTERVAL value addunit)
Parameter Values
Parameter  Description 

date  Required. The date that has to be changed. 
days  Required. The number of days to add to the current date. 
value  Required. The value of the extra time/date interval Both positive and negative numbers are permitted. 
addunit  Required. The interval that has to be provided. One of the values listed below may be used:

Technical Details
Examples:
Example 1: This example to add 15 days:
SELECT ADDDATE('20220215', 15),
ADDDATE('20220215', 15);
Result is
ADDDATE('20220215', 15)  ADDDATE('20220215', 15) 

20220302  20220131 
The result adds 15 days from specified date, and another minus 15 days from specified date.
Example 2: Return the date after adding days, hours, and minutes in this example:
SELECT ADDDATE('20220201', INTERVAL 15 DAY),
ADDDATE('20220201', INTERVAL 15 HOUR),
ADDDATE('20220201', INTERVAL 15 MINUTE);
Result is
ADDDATE('20220201', INTERVAL 15 DAY)  ADDDATE('20220201', INTERVAL 15 HOUR)  ADDDATE('20220201', INTERVAL 15 MINUTE) 

20220216  20220201 15:00:00  20220201 00:15:00 
We add 15 days, hours, and minutes to the supplied date based on the outcome.
Example 3: Return the date after substract days, hours, and minutes in this example:
SELECT ADDDATE('20220201', INTERVAL 15 DAY),
ADDDATE('20220201', INTERVAL 15 MONTH),
ADDDATE('20220201', INTERVAL 15 MINUTE);
Result is
ADDDATE('20220201', INTERVAL 15 DAY)  ADDDATE('20220201', INTERVAL 15 MONTH)  ADDDATE('20220201', INTERVAL 15 MINUTE) 

20220117  20201101  20220131 23:45:00 
We substract 15 days, hours, and minutes to the supplied date based on the outcome.
MySQL ADDTIME() Function
The ADDTIME()
function returns the result of adding a given time value to another given time.
Syntax:
The syntax for the MySQL ADDTIME() function is as follows:
ADDTIME(time_expr, INTERVAL value addunit)
Parameter Values
Parameter  Description 

datetime  Required. The date that has to be changed. 
addtime  Required. Add the time interval to datetime. Both positive and negative numbers are valid. 
Technical Details
Works in:  From MySQL 4.0 

Examples:
Example 1: Adding seconds to the stated time in this example.:
SELECT ADDTIME("20220108 08:14:11", "12");
Result is
We appended 12 seconds to the supplied datetime parameter based on the results.
Example 2: Returning a datetime by multiplying a time by 5 hours, 30 minutes, 8 seconds, and 10 microseconds is an instance:
SELECT ADDTIME("20220118 03:54:12.000005", "5:30:8.000010");
Result is
The result is used to append the supplied date and time by adding 5 hours, 30 minutes, 8 seconds, and 10 microseconds.
Example 3: After appending 3 days, 12 hours, 40 minutes, 15 seconds, and 22 microseconds to a time, the instance returns the datetime:
SELECT ADDTIME("20220325 09:34:21.000001", "3 12:40:15.000022");
Result is
In this result, you can see that we have added 3 days, 12 hours, 40 minutes, 15 seconds, and 22 microseconds to the specific date and time.
MySQL CURDATE() Function
The CURDATE()
function in MySQL outputs the current date in the YYYYMMDD or YYYYMMDD format.
Note:This method is the same as CURRENT_DATE()
.
Syntax:
The syntax for the MySQL CURDATE() function is as follows:
CURDATE()
Technical Details
Works in:  From MySQL 4.0 

Examples:
Example 1: The example is to Return the current date:
SELECT CURDATE();
Result is
This result is used to give a current date.
Example 2: The current date is returned in this example. 0 times multiplied:
SELECT CURDATE() + 0;
Result is
This result is used to display the current date in number format.
Example 3: The current date is returned in this example. 5 times multiplied:
SELECT CURDATE() + 5;
Result is
Number formatting, the result is used to add 5 days to the current date.
MySQL CURRENT_DATE() Function
The CURRENT_DATE()
function in MySQL returns the system's current date in the format YYYYMMDD or YYYYMMDD.
Note:The CURDATE()
method is the same as this.
Syntax:
The syntax for the MySQL CURRENT_DATE() function is as follows:
CURRENT_DATE()
Technical Details
Works in:  From MySQL 4.0 

Examples:
Example 1: The current date example will be returned:
SELECT CURRENT_DATE();
Result is
The function is used to return the currentdate.
Example 2: The example to return the current date multiplied by two:
SELECT CURRENT_DATE() + 2;
Result is
The function is used to add 2 days in currentdate in number format.
MySQL CURRENT_TIME() Function
The current time of the system is returned by MySQL's CURRENT_TIME()
function.
Note: The time is returned as "HHMMSS" (string) or as HHMMSS.uuuuuu (numeric).
Note: This is the same as the CURTIME()
function.
Syntax:
The syntax for the MySQL CURRENT_TIME() function is as follows:
CURRENT_TIME()
Technical Details
Works in:  From MySQL 4.0 

Examples:
Example 1: Returning the current time is an example:
SELECT CURRENT_TIME();
Result is
The function gives you the current system time.
Example 2: Returning current time + 5 is an example:
SELECT CURRENT_TIME() + 5;
Result is
The result of CURRENT_TIME() + 5 is in the hhmmss format.
MySQL CURRENT_TIMESTAMP() [fsp]Function
The current date and time are returned by MySQL's CURRENT_TIMESTAMP()
function.
Note: The date and time is returned as "YYYYMMDD HHMMSS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
Syntax:
The syntax for the MySQL CURRENT_TIMESTAMP() function is as follows:
CURRENT_TIMESTAMP()
Technical Details
Works in:  From MySQL 4.0 

Examples:
Example 1: To find the current date and time, type in the following example:
SELECT CURRENT_TIMESTAMP();
Result is
The function returns current timestamp.
Example 2: The example to return the current date and time + 1:
SELECT CURRENT_TIMESTAMP() + 3;
Result is
From the result, CURRENT_TIMESTAMP() + 3, so CURRENT_TIMESTAMP() returns a number in YYYYMMDDhhmmss format.
MySQL CURTIME() Function
The MySQL CURTIME()
method returns the system's current time.
Note: The time is returned as "HHMMSS" (string) or as HHMMSS.uuuuuu (numeric).
Note: This method is the same as CURRENT_TIME()
.
Syntax:
The syntax for the MySQL CURTIME() function is as follows:
CURTIME()
Technical Details
Works in:  From MySQL 4.0 

Examples:
Example 1: Returning the current time is an example:
SELECT CURTIME();
Result is
Example 2: Returning the current time + 1 is an example:
SELECT CURTIME() + 2;
Result is
The format of the result of CURTIME() + 2 is hhmmss. add 1 second to the current system time.
MySQL DATE() Function
The DATE()
method in MySQL retrieves and outputs the date component of a datetime item.
Syntax:
The syntax for the MySQL DATE() function is as follows:
DATE(expr)
Parameter Values
Parameter  Description 

expr  Required. A valid date/datetime value. NULL is returned if the expression is not a date or a datetime. 
Technical Details
Works in:  From MySQL 4.0 

Examples:
Example 1: Remove the section about the date:
SELECT DATE('20220115'),
DATE('20220105 11:20:10'),
DATE(NOW());
Result is
DATE('20220115')  DATE('20220105 11:20:10')  DATE(NOW()) 

1/15/2022  1/5/2022  5/20/2022 
The date(NOW()) function extracts the date from the current timestamp, while the date() function extracts the date from the specified timestamp.
Example 2: Let's check null and NotValid in this example:
SELECT DATE('20220111'),
DATE(NULL);
Result is
DATE('20220230')  DATE(NULL) 

NULL  NULL 
The output is NULL because 30022022 is not a valid date, and also if there is a null value in the parameter, the result will be NULL.
Example 3: The example to extract the date part:
Let's take DATESIMPLE table to execute below query:
Name  DOB 

Vinoth Kumar  1997/08/22 18:46:30 
Padmavathi  2009/09/25 13:46:34 
The query will be look like this,
SELECT DATE(DOB) FROM DATESIMPLE;
Result is
DATE(DOB) 

8/22/1997 
9/25/2009 
The output retrieves the date from the Dob column.
MySQL DATEDIFF() Function
Main Article : MySql DATEDIFF() Function
The DATEDIFF() method calculates the difference in days between two dates.
Syntax
DATEDIFF(date1, date2)
Parameter Values
Parameter  Description 

date1, date2  Required. Two dates to calculate the number of days between. (date1  date2) 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Return the number of days that separate two dates:
SELECT DATEDIFF("20170625", "20170615");
Result is 10
Example 2: The difference in days between two dates is returned:
SELECT DATEDIFF("20170625 09:34:21", "20170615 15:25:35");
Result is 10
Example 3: Return the number of days between two date values:
SELECT DATEDIFF("20170101", "20161224");
Result is 8
MySQL DATE_ADD() Function
Main Article : MySql DATE_ADD() Function
The DATE ADD() method takes a date and adds a time/date interval to it before returning the date.
Syntax
DATE_ADD(date, INTERVAL value addunit)
Parameter Values
Parameter  Description 

date  Required. The date to be modified 
value  Required. The time/date interval to be added's value. Positive and negative values are both acceptable. 
addunit  Required. The interval to be added. One of the following values is possible:

Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Return the date after adding 10 days:
SELECT DATE_ADD("20170615", INTERVAL 10 DAY);
Result is 20170625
Example 2: Return the date after adding 15 minutes:
SELECT DATE_ADD("20170615 09:34:21", INTERVAL 15 MINUTE);
Result is 20170615 09:49:21
Example 3: Subtract 3 hours from a date and recalculate the date value.
SELECT DATE_ADD("20170615 09:34:21", INTERVAL 3 HOUR);
Result is 20170615 06:34:21
Example 4: Subtract two months from a date and recalculate:
SELECT DATE_ADD("20170615", INTERVAL 2 MONTH);
Result is 20170415
MySQL DATE_FORMAT() Function
The DATE FORMAT() method formats a date according to the parameters.
Syntax
DATE_FORMAT(date, format)
Parameter Values
Parameter  Description  

date  Required. The date to be formatted  
format  Required. The format to use. Can be one or a
combination of the following values:

Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Format a date with Year only
SELECT DATE_FORMAT("20170615", "%Y");
Result is 2017
Example 2: Format a date with Month, Day and Year only
SELECT DATE_FORMAT("20170615", "%M %d %Y");
Result is June 15 2017
Example 3: Format a date with Dayname, Month, Day and Year only
SELECT DATE_FORMAT("20170615", "%W %M %e %Y");
Result is Thursday June 15 2017
Example 4: Format a date with Dayname, Month name, Day and Year only
SELECT DATE_FORMAT(BirthDate, "%W %M %e %Y") FROM Employees;
Result is Sunday December 8 1968
MySQL DATE_SUB() Function
The DATE SUB() method takes a date and subtracts a time/date interval from it before returning the date.
Syntax
DATE_SUB(date, INTERVAL value interval)
Parameter Values
Parameter  Description 

date  Required. The date to be modified 
value  Required. The value of the time/date interval to subtract. Both positive and negative values are allowed 
interval  Required. The type of interval to subtract. Can be one of the following
values:

Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Subtract 10 days from a date and recalculate it as follows:
SELECT DATE_SUB("20170615", INTERVAL 10 DAY);
Result is 20170605
Example 2: Subtract 15 minutes from a date and recalculate it as follows:
SELECT DATE_SUB("20170615 09:34:21", INTERVAL 15 MINUTE);
Result is 20170605
Example 3: Subtract 3 hours from a date and recalculate it as follows:
SELECT DATE_SUB("20170615 09:34:21", INTERVAL 3 HOUR);
Result is 20170605
Example 4: Return the date after adding two months:
SELECT DATE_SUB("20170615", INTERVAL 2 MONTH);
Result is 20170815
MySQL DAY() Function
Main Article : MySql DAY() Function
For a given date, the DAY() method returns the month's day (a number from 1 to 31).
Note:The DAYOFMONTH() method is equivalent to this.
Syntax
DAY(date)
Parameter Values
Parameter  Description 

date  Required. The date to extract the day from 
Technical Details
Works in:  From MySQL 4.0 

Example
Example 1: If you're looking for a date, return the day of the month:
SELECT DAY("20170615");
Result is 15
Example 2: If you're looking for a date, return the day of the month:
Result is 15
Example 3: For the current system date, get the day of the month:
SELECT DAY(CURDATE());
Result is 29
MySQL DAYNAME() Function
Main Article : MySql DAYNAME() Function
The weekday name for a given date is returned by the DAYNAME() method.
Syntax
DAYNAME(date)
Parameter Values
Parameter  Description 

date  Required. The date to extract the weekday name from 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: For a given date, return the name of the weekday:
SELECT DAYNAME("20170615");
Result is Thursday
Example 2: For a given date, get the weekday name:
SELECT DAYNAME("20170615 09:34:21");
Result is Thursday
Example 3: For the current system date, get the weekday name:
SELECT DAYNAME(CURDATE());
Result is Thursday
MySQL DAYOFMONTH() Function
The DAYOFMONTH() method returns the date's month day (a number from 1 to 31).
Note: The DAY() method is the same as this one.
Syntax
DAYOFMONTH(date)
Parameter Values
Parameter  Description 

date  Required. The date to extract the day from 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: If you're looking for a date, return the day of the month:
SELECT DAYOFMONTH("20170615");
Result is 15
Example 2: If you're looking for a date, return the day of the month:
SELECT DAYOFMONTH("20170615 09:34:21");
Result is 15
Example 3: For the current system date, get the day of the month:
SELECT DAYOFMONTH(CURDATE());
Result is 30
MySQL DAYOFWEEK() Function
The weekday index for a given date is returned by the DAYOFWEEK() function (a number from 1 to 7).
Note:1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.
Syntax
DAYOFWEEK(date)
Parameter Values
Parameter  Description 

date  Required. The date to return the weekday index from 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: For a given date, get the weekday index:
SELECT DAYOFWEEK("20170615");
Result is 5
Example 2: Return the weekday index for a date:
SELECT DAYOFWEEK("20170615 09:34:21");
Result is 5
Example 3: Return the weekday index for the current system date:
SELECT DAYOFWEEK(CURDATE());
Result is 5
MySQL DAYOFYEAR() Function
Main Article : MySql DAYOFYEAR() Function
For a given date, the DAYOFYEAR() method returns the day of the year (a number from 1 to 366).
Syntax
DAYOFYEAR(date)
Parameter Values
Parameter  Description 

date  Required. The date to return the day of the year from 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: For a date, go back to the beginning of the year:
SELECT DAYOFYEAR("20170615");
Result is 166
Example 2: For a date, go back to the beginning of the year:
SELECT DAYOFYEAR("20170101");
Result is 1
Example 3: Return the day of the year for the current system date:
SELECT DAYOFYEAR(CURDATE());
Result is 364
MySQL EXTRACT() Function
Main Article : MySql EXTRACT() Function
The EXTRACT() method takes a portion of a date and extracts it.
Syntax
EXTRACT(part FROM date)
Parameter Values
Parameter  Description 

part  Required. The part to extract. Can be one of the following:

date  Required. The date to extract a part from 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: To get the month out of a date, use the following formula
SELECT EXTRACT(MONTH FROM "20170615");
Result is 6
Example 2: To get the week of a date, use the following formula:
SELECT EXTRACT(WEEK FROM "20170615");
Result is 24
Example 3: To get the minute from a datetime, use the following formula:
SELECT EXTRACT(MINUTE FROM "20170615 09:34:21");
Result is 34
Example 4: From a datetime, get the year and month:
SELECT EXTRACT(YEAR_MONTH FROM "20170615 09:34:21");
Result is 201706
MySQL FROM_DAYS() Function
From a numeric datevalue, the FROM DAYS() method outputs a date.
Only dates in the Gregorian calendar should be used using the FROM DAYS() method.
Note:The TO DAYS() function is the inverse of this.
Syntax
FROM_DAYS(number)
Parameter Values
Parameter  Description 

number  Required. The numeric day to convert to a date 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: From a numeric representation of the day, get a date:
SELECT FROM_DAYS(685467);
Result is 18760929
Example 2: Return a date from a day's numerical representation:
SELECT FROM_DAYS(780500);
Result is 21361208
MySQL HOUR() Function
Main Article : MySql HOUR() Function
The hour portion of a date is returned by the HOUR() method (from 0 to 838).
Syntax
HOUR(datetime)
Parameter Values
Parameter  Description 

datetime  Required. The datetime value to extract the hour from 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: The hour portion of a datetime is returned:
SELECT HOUR("20170620 09:34:00");
Result is 9
Example 2: Return the hour part of a datetime:
SELECT HOUR("838:59:59");
Result is 838
MySQL LAST_DAY() Function
The LAST DAY() method returns the month's last day for a specified date.
Syntax
LAST_DAY(date)
Parameter Values
Parameter  Description 

date  Required. The date to extract the last day of the month from 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: For the given date, find the final day of the month:
SELECT LAST_DAY("20170620");
Result is 20170630
Example 2: For the given date, calculate the final day of the month:
SELECT LAST_DAY("20170210 09:34:00");
Result is 20170228
MySQL LOCALTIME() Function
The current date and time are returned via the LOCALTIME() method.
Note:The date and time is returned as "YYYYMMDD HHMMSS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
Syntax
LOCALTIME()
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Current date and time should be returned:
SELECT LOCALTIME();
Result is 20211230 07:06:14
Example 2: Return current date and time + 1:
SELECT LOCALTIME() + 1;
Result is 20211230070553
MySQL LOCALTIMESTAMP() Function
The current date and time are returned by the LOCALTIMESTAMP() method.
Note:The date and time is returned as "YYYYMMDD HHMMSS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
Syntax
LOCALTIMESTAMP()
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Return the current time and date:
SELECT LOCALTIMESTAMP();
Result is 20211230 07:59:17
Example 2: Return the current time and date:
SELECT LOCALTIMESTAMP() + 1;
Result is 20211230075852
MySQL MAKEDATE() Function
The MAKEDATE() method generates and returns a date depending on the year and the number of days specified.
Syntax
MAKEDATE(year, day)
Parameter Values
Parameter  Description 

year  Required. A year (4digits) 
day  Required. The year's day is represented by a number. 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Create and return a date depending on the value of a year and the number of days:
SELECT MAKEDATE(2017, 3);
Result is 20170103
Example 2: Create and return a date depending on the value of a year and the number of days:
SELECT MAKEDATE(2017, 175);
Result is 20170624
Example 3: Create and return a date depending on the value of a year and the number of days:
SELECT MAKEDATE(2017, 100);
Result is 20170410
Example 4: Create and return a date depending on the value of a year and the number of days:
SELECT MAKEDATE(2017, 366);
Result is 20180101
MySQL MAKETIME() Function
The MAKETIME() method generates and returns a time depending on the input values of hour, minute, and second.
Syntax
MAKETIME(hour, minute, second)
Parameter Values
Parameter  Description 

hour  Required. The hour value 
minute  Required. The minute value 
second  Required. The seconds value 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Make a time value from an hour, minute, and second value and return it:
SELECT MAKETIME(11, 35, 4);
Result is 11:35:04
Example 2: Make a time value based on an hour, minute, and second value and return it:
SELECT MAKETIME(16, 1, 0);
Result is 16:01:00
Example 3: Make a time value from an hour, minute, and second value and return it:
SELECT MAKETIME(21, 59, 59);
Result is 21:59:59
Example 4: Create a time value based on an hour, minute, and second value and return it:
SELECT MAKETIME(838, 59, 59);
Result is 838:59:59
MySQL MICROSECOND() Function
The MICROSECOND() method returns a time/microsecond datetime's portion (from 0 to 999999).
Syntax
MICROSECOND(datetime)
Parameter Values
Parameter  Description 

datetime  Required. The time or datetime from which the microsecond will be extracted 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Return the portion of a datetime that is in microseconds:
SELECT MICROSECOND("20170620 09:34:00.000023");
Result is 23
Example 2: Return the part of a time value that is in microseconds:
SELECT MICROSECOND("23:59:59.000045");
Result is 45
MySQL MINUTE() Function
Main Article : MySql MINUTE() Function
The MINUTE() method returns a time/minute datetime's component (from 0 to 59).
Syntax
MINUTE(datetime)
Parameter Values
Parameter  Description 

datetime  Required. The minute is extracted from the time or datetime. 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: The minute portion of a datetime value is returned:
SELECT MINUTE("20170620 09:34:00");
Result is 34
Example 2: The minute portion of a time value is returned:
SELECT MINUTE("23:59:59");
Result is 59
MySQL MONTH() Function
Main Article : MySql MONTH() Function
For a given date, the MONTH() method returns the month part (a number from 1 to 12).
Syntax
MONTH(date)
Parameter Values
Parameter  Description 

date  Required. The month should be extracted from a date or datetime. 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: The month component of a date should be returned:
SELECT MONTH("20170615");
Result is 6
Example 2: The month portion of a date should be returned:
SELECT MONTH("20170615 09:34:21");
Result is 6
Example 3: Return the current system date's month component:
SELECT MONTH(CURDATE());
Result is 12
MySQL MONTHNAME() Function
Main Article : MySql MONTHNAME() Function
For a given date, the MONTHNAME() method returns the month's name.
Syntax
MONTHNAME(date)
Parameter Values
Parameter  Description 

date  Required. The month name will be extracted from the date or datetime value. 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: For a given date, return the month name:
SELECT MONTHNAME("20170615");
Result is June
Example 2: For a given date, return the month's name:
SELECT MONTHNAME("20170615 09:34:21");
Result is June
Example 3: For the current system date, return the month's name as follows:
SELECT MONTHNAME(CURDATE());
Result is December
MySQL NOW() Function
Main Article : MySql NOW() Function
The current date and time are returned by the NOW() method.
Note:The date and time is returned as "YYYYMMDD HH:MM:SS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
Syntax
NOW()
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Return the current time and date:
SELECT NOW();
Result is 20211230 11:13:19
Example 2: Return current date and time + 1:
SELECT NOW() + 1;
Result is 20211230111242
MySQL PERIOD_ADD() Function
The PERIOD ADD() method extends a period by a specified number of months.
The PERIOD ADD() method returns a YYYYMM formatted output.
Syntax
PERIOD_ADD(period, number)
Parameter Values
Parameter  Description 

period  Required. A period. Format: YYMM or YYYYMM 
number  Required. The number of months that should be added to the timeframe. Positive and negative values are both acceptable. 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: To make a period longer, add a specific number of months:
SELECT PERIOD_ADD(201703, 5);
Result is 201708
Example 2: To make a period longer, add a certain number of months:
SELECT PERIOD_ADD(201703, 15);
Result is 201806
Example 3: To make a period longer, add a specific number of months:
SELECT PERIOD_ADD(201703, 2);
Result is 201701
MySQL PERIOD_DIFF() Function
The difference between two periods is returned by the PERIOD DIFF() function. The outcome will be known in a few months.
Note:Periods 1 and 2 should be formatted in the same way.
Syntax
PERIOD_DIFF(period1, period2)
Parameter Values
Parameter  Description 

period1  Required. A period. Format: YYMM or YYYYMM 
period2  Required. Another period. Format: YYMM or YYYYMM 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Calculate the difference between two periods as follows:
SELECT PERIOD_DIFF(201710, 201703);
Result is 7
Example 2: Calculate the difference between two periods as follows:
SELECT PERIOD_DIFF(201703, 201803);
Result is 12
Example 3: Calculate the difference between two periods as follows:
SELECT PERIOD_DIFF(1703, 1612);
Result is 3
MySQL QUARTER() Function
For a given date, the QUARTER() method returns the year's quarter (a number from 1 to 4).
 JanuaryMarch returns 1
 AprilJune returns 2
 JulySep returns 3
 OctDec returns 4
Syntax
QUARTER(date)
Parameter Values
Parameter  Description 

date  Required. The date or datetime from which the quarter will be extracted. 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: For the date, please return the year's quarter:
SELECT QUARTER("20170615");
Result is 2
Example 2: For the date, please return the year's quarter:
SELECT QUARTER("20170101 09:34:21");
Result is 1
Example 3: For the date, please return the year's quarter:
SELECT QUARTER(CURDATE());
Result is 4
MySQL SECOND() Function
Main Article : MySql SECOND() Function
SECOND() returns the seconds component of a time/datetime (from 0 to 59).
Syntax
SECOND(datetime)
Parameter Values
Parameter  Description 

datetime  Required. The time or datetime to extract the second from 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: The seconds component of a datetime value is returned:
SELECT SECOND("20170620 09:34:00.000023");
Result is 0
Example 2: Return the part of a time value that is in seconds:
SELECT SECOND("23:59:59");
Result is 59
MySQL SUBDATE() Function
The SUBDATE() method takes a date and subtracts a time/date interval from it before returning the date.
Syntax
SUBDATE(date, INTERVAL value unit)
Parameter Values
Parameter  Description 

date  Required. The original date 
days  Required. The number of days to deduct from the current date 
value  Required. Subtract the value of the time/date period. Positive and negative values are both acceptable. 
unit  Required. The interval's kind. One of the following values is possible:

Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Subtract 10 days from a date and recalculate it as follows:
SELECT SUBDATE("20170615", INTERVAL 10 DAY);
Result is 20170605
Example 2: Subtract 15 minutes from a date and recalculate it as follows:
SELECT SUBDATE("20170615 09:34:21", INTERVAL 15 MINUTE);
Result is 20170615 09:19:21
Example 3: Subtract 3 hours from a date and recalculate it as follows:
SELECT SUBDATE("20170615 09:34:21", INTERVAL 3 HOUR);
Result is 20170615 06:34:21
Example 4: Return the date after adding two months:
SELECT SUBDATE("20170615", INTERVAL 2 MONTH);
Result is 20170815
MySQL SUBTIME() Function
The SUBTIME() method subtracts time from a time/datetime expression before returning the result.
Syntax
SUBTIME(datetime, time_interval)
Parameter Values
Parameter  Description 

datetime  Required. The time or datetime to be modified 
time_interval  Required. The time period from which datetime should be subtracted. Positive and negative values are both acceptable. 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Subtract 5.000001 seconds from the datetime to get the
Result is
SELECT SUBTIME("20170615 10:24:21.000004", "5.000001");
Result is 20170615 10:24:16.000003
Example 2: Return the datetime by subtracting 3 hours, 2 minutes, and 5.000001 seconds:
SELECT SUBTIME("20170615 10:24:21.000004", "3:2:5.000001");
Result is 20170615 07:22:16.000003
Example 3: Subtract 5 seconds from the time and recalculate:
SELECT SUBTIME("10:24:21", "5");
Result is 10:24:16
Example 4: Subtract 3 minutes from the time and recalculate:
SELECT SUBTIME("10:24:21", "300");
Result is 10:21:21
Example 5: Add 3 hours, 2 minutes, and 5 seconds, and return the time:
SELECT SUBTIME("10:24:21", "3:2:5");
Result is 13:26:26
MySQL TIME() Function
TIME() retrieves the time component of a specified time/datetime.
Note:If expression is not a datetime/time, this method returns "00:00:00," else NULL.
Syntax
TIME(expression)
Parameter Values
Parameter  Description 

expression  Required. The time/datetime from which to obtain the time 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: To get the time portion of a time expression, do the following:
SELECT TIME("19:30:10");
Result is 19:30:10
Example 2: Take the time component out of the datetime expression:
SELECT TIME("20170815 19:30:10");
Result is 19:30:10
Example 3: From the datetime expression, extract the time component:
SELECT TIME("20170815 19:30:10.000001");
Result is 19:30:10.000001
Example 4: Extract the time part from NULL:
SELECT TIME(NULL);
Result is NULL
MySQL TIMEDIFF() Function
The TIMEDIFF() method compares two time/datetime expressions and returns the difference.
Note:The format of time1 and time2 should be the same, and the computation should be time1  time2.
Syntax
TIMEDIFF(time1, time2)
Parameter Values
Parameter  Description 

time1  Required. A time value 
time2  Required. Another time value 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: The difference between two time expressions is returned:
SELECT TIMEDIFF("13:10:11", "13:10:10");
Result is 00:00:01
Example 2: The difference between two datetime expressions is returned:
SELECT TIMEDIFF("20170625 13:10:11", "20170615 13:10:10");
Result is 240:00:01
MySQL TIMESTAMP() Function
The TIMESTAMP() method generates a datetime value from a date or a datetime value.
Note:This method adds the second parameter to the first before returning a datetime value if there are two arguments supplied.
Syntax
TIMESTAMP(expression, time)
Parameter Values
Parameter  Description 

expression  Required. A date or datetime value 
time  Optional. A time value to add to expression 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Based on the parameters, return a datetime value:
SELECT TIMESTAMP("20170723", "13:10:11");
Result is 20170723 13:10:11
Example 2: Using the parameters, return a datetime value:
SELECT TIMESTAMP("20170723");
Result is 20170723 00:00:00
MySQL TO_DAYS() Function
The TO DAYS() method calculates the number of days between a date and the beginning of the year (date "00000000").
The TO DAYS() method may only be used with Gregorian calendar dates.
Note: This function is the polar opposite of FROM DAYS().
Syntax
TO_DAYS(date)
Parameter Values
Parameter  Description 

date  Required. The given date 
Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Return the number of days between the current date and the beginning of the year 0:
SELECT TO_DAYS("20170620");
Result is 736865
Example 2: Return the number of days that have passed between the current date and the year zero:
SELECT TO_DAYS("20170620 09:34:00");
Result is 736865
MySQL WEEK() Function
The week number for a given date is returned by the WEEK() function (a number from 0 to 53).
Syntax
WEEK(date, firstdayofweek)
Parameter Values
Parameter  Description 

date  Required. The date or datetime to extract the week number form 
firstdayofweek 
Optional. Specifies what day the week starts on. Can be one of the following:

Technical Details
Works in:  From MySQL 4.0 

Examples
Example 1: Return the week number for a date:
SELECT WEEK("20170615");
Result is 24
Example 2: Return the week number for a date:
SELECT WEEK("20171025");
Result is 43
Example 3: For the current system date, get the week number:
SELECT WEEK(CURDATE());
Result is 52
MySQL WEEKDAY() Function
For a given date, the WEEKDAY() method returns the weekday number.
Note: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday.
Syntax
WEEKDAY(date)
Parameter Values
Parameter  Description 

date  Required. The date or datetime to extract the weekday number from 
Technical Details
Works in:  From MySQL 4.0 

Example
For a given date, return the weekday number:
SELECT WEEKDAY("20170615");
Result is 3
MySQL YEARWEEK() Function
The YEARWEEK() method returns the year and week number for a specified date (a integer between 0 and 53).
Syntax
YEARWEEK(date, firstdayofweek)
Parameter Values
Parameter  Description 

date  Required. The date or datetime value to extract the year and week number from 
firstdayofweek 
Optional. The day on which the week begins is specified. One of the following possibilities:

Technical Details
Works in:  From MySQL 4.0 

Example
For a date, return the year and week number:
SELECT YEARWEEK("20170615");
Result is 201724