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: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH

Technical Details

Examples:-

Example 1: This example to add 15 days:

SELECT ADDDATE('2022-02-15', 15),
ADDDATE('2022-02-15', -15);

Result is

ADDDATE('2022-02-15', 15)	ADDDATE('2022-02-15', -15)
2022-03-02	2022-01-31

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('2022-02-01', INTERVAL 15 DAY),
ADDDATE('2022-02-01', INTERVAL 15 HOUR),
ADDDATE('2022-02-01', INTERVAL 15 MINUTE);

Result is

ADDDATE('2022-02-01', INTERVAL 15 DAY)	ADDDATE('2022-02-01', INTERVAL 15 HOUR)	ADDDATE('2022-02-01', INTERVAL 15 MINUTE)
2022-02-16	2022-02-01 15:00:00	2022-02-01 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('2022-02-01', INTERVAL -15 DAY),
ADDDATE('2022-02-01', INTERVAL -15 MONTH),
ADDDATE('2022-02-01', INTERVAL -15 MINUTE);

Result is

ADDDATE('2022-02-01', INTERVAL -15 DAY)	ADDDATE('2022-02-01', INTERVAL -15 MONTH)	ADDDATE('2022-02-01', INTERVAL -15 MINUTE)
2022-01-17	2020-11-01	2022-01-31 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("2022-01-08 08:14:11", "12");

Result is

2022-01-08 08:14:23

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("2022-01-18 03:54:12.000005", "5:30:8.000010");

Result is

2022-01-18 09:24:20.000015

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("2022-03-25 09:34:21.000001", "3 12:40:15.000022");

Result is

2022-03-28 22:14:36.000023

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 YYYY-MM-DD 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

5/18/2022 12:00:00 AM

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

20220518

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

20220523

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 YYYY-MM-DD 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

5/18/2022 12:00:00 AM

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

20220520

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 "HH-MM-SS" (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

12:40:17

The function gives you the current system time.

Example 2: Returning current time + 5 is an example:

SELECT CURRENT_TIME() + 5;

Result is

124260

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.

If the fsp option is used to provide a fractional second precision from 0 to 6, the return value has a fractional second component with many digits.

Note: The date and time is returned as "YYYY-MM-DD HH-MM-SS" (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

5/18/2022 12:56:15 PM

The function returns current timestamp.

Example 2: The example to return the current date and time + 1:

SELECT CURRENT_TIMESTAMP() + 3;

Result is

20220518090448

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 "HH-MM-SS" (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

01:15:48

Example 2: Returning the current time + 1 is an example:

SELECT CURTIME() + 2;

Result is

91849

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('2022-01-15'),
DATE('2022-01-05 11:20:10'),
DATE(NOW());

Result is

DATE('2022-01-15')	DATE('2022-01-05 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('2022-01-11'),
DATE(NULL);

Result is

DATE('2022-02-30')	DATE(NULL)
NULL	NULL

The output is NULL because 30-02-2022 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("2017-06-25", "2017-06-15");

Result is 10

Example 2:- The difference in days between two dates is returned:

SELECT DATEDIFF("2017-06-25 09:34:21", "2017-06-15 15:25:35");

Result is 10

Example 3:- Return the number of days between two date values:

SELECT DATEDIFF("2017-01-01", "2016-12-24");

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: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH

Technical Details

Works in:	From MySQL 4.0

Examples

Example 1:- Return the date after adding 10 days:

SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);

Result is 2017-06-25

Example 2:- Return the date after adding 15 minutes:

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);

Result is 2017-06-15 09:49:21

Example 3:- Subtract 3 hours from a date and recalculate the date value.

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);

Result is 2017-06-15 06:34:21

Example 4:- Subtract two months from a date and recalculate:

SELECT DATE_ADD("2017-06-15", INTERVAL -2 MONTH);

Result is 2017-04-15

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:

Format	Description
%a	Abbreviated weekday name (Sun to Sat)
%b	Abbreviated month name (Jan to Dec)
%c	Numeric month name (0 to 12)
%D	Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%d	Day of the month as a numeric value (01 to 31)
%e	Day of the month as a numeric value (0 to 31)
%f	Microseconds (000000 to 999999)
%H	Hour (00 to 23)
%h	Hour (00 to 12)
%I	Hour (00 to 12)
%i	Minutes (00 to 59)
%j	Day of the year (001 to 366)
%k	Hour (0 to 23)
%l	Hour (1 to 12)
%M	Month name in full (January to December)
%m	Month name as a numeric value (00 to 12)
%p	AM or PM
%r	Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S	Seconds (00 to 59)
%s	Seconds (00 to 59)
%T	Time in 24 hour format (hh:mm:ss)
%U	Week where Sunday is the first day of the week (00 to 53)
%u	Week where Monday is the first day of the week (00 to 53)
%V	Week where Sunday is the first day of the week (01 to 53). Used with %X
%v	Week where Monday is the first day of the week (01 to 53). Used with %x
%W	Weekday name in full (Sunday to Saturday)
%w	Day of the week where Sunday=0 and Saturday=6
%X	Year for the week where Sunday is the first day of the week. Used with %V
%x	Year for the week where Monday is the first day of the week. Used with %v
%Y	Year as a numeric, 4-digit value
%y	Year as a numeric, 2-digit value

Technical Details

Works in:	From MySQL 4.0

Examples

Example 1:- Format a date with Year only

SELECT DATE_FORMAT("2017-06-15", "%Y");

Result is 2017

Example 2:- Format a date with Month, Day and Year only

SELECT DATE_FORMAT("2017-06-15", "%M %d %Y");

Result is June 15 2017

Example 3:- Format a date with Dayname, Month, Day and Year only

SELECT DATE_FORMAT("2017-06-15", "%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: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH

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("2017-06-15", INTERVAL 10 DAY);

Result is 2017-06-05

Example 2:- Subtract 15 minutes from a date and recalculate it as follows:

SELECT DATE_SUB("2017-06-15 09:34:21", INTERVAL 15 MINUTE);

Result is 2017-06-05

Example 3:- Subtract 3 hours from a date and recalculate it as follows:

SELECT DATE_SUB("2017-06-15 09:34:21", INTERVAL 3 HOUR);

Result is 2017-06-05

Example 4:- Return the date after adding two months:

SELECT DATE_SUB("2017-06-15", INTERVAL -2 MONTH);

Result is 2017-08-15

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("2017-06-15");

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("2017-06-15");

Result is Thursday

Example 2:- For a given date, get the weekday name:

SELECT DAYNAME("2017-06-15 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("2017-06-15");

Result is 15

Example 2:- If you're looking for a date, return the day of the month:

SELECT DAYOFMONTH("2017-06-15 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("2017-06-15");

Result is 5

Example 2:- Return the weekday index for a date:

SELECT DAYOFWEEK("2017-06-15 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("2017-06-15");

Result is 166

Example 2:- For a date, go back to the beginning of the year:

SELECT DAYOFYEAR("2017-01-01");

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: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH
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 "2017-06-15");

Result is 6

Example 2:- To get the week of a date, use the following formula:

SELECT EXTRACT(WEEK FROM "2017-06-15");

Result is 24

Example 3:- To get the minute from a datetime, use the following formula:

SELECT EXTRACT(MINUTE FROM "2017-06-15 09:34:21");

Result is 34

Example 4:- From a datetime, get the year and month:

SELECT EXTRACT(YEAR_MONTH FROM "2017-06-15 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 1876-09-29

Example 2:- Return a date from a day's numerical representation:

SELECT FROM_DAYS(780500);

Result is 2136-12-08

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("2017-06-20 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("2017-06-20");

Result is 2017-06-30

Example 2:- For the given date, calculate the final day of the month:

SELECT LAST_DAY("2017-02-10 09:34:00");

Result is 2017-02-28

MySQL LOCALTIME() Function

The current date and time are returned via the LOCALTIME() method.

Note:The date and time is returned as "YYYY-MM-DD HH-MM-SS" (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 2021-12-30 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 "YYYY-MM-DD HH-MM-SS" (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 2021-12-30 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 (4-digits)
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 2017-01-03

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 2017-06-24

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 2017-04-10

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 2018-01-01

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("2017-06-20 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("2017-06-20 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("2017-06-15");

Result is 6

Example 2:- The month portion of a date should be returned:

SELECT MONTH("2017-06-15 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("2017-06-15");

Result is June

Example 2:- For a given date, return the month's name:

SELECT MONTHNAME("2017-06-15 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 "YYYY-MM-DD 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 2021-12-30 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).

January-March returns 1
April-June returns 2
July-Sep returns 3
Oct-Dec 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("2017-06-15");

Result is 2

Example 2:- For the date, please return the year's quarter:

SELECT QUARTER("2017-01-01 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("2017-06-20 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: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH

Technical Details

Works in:	From MySQL 4.0

Examples

Example 1:- Subtract 10 days from a date and recalculate it as follows:

SELECT SUBDATE("2017-06-15", INTERVAL 10 DAY);

Result is 2017-06-05

Example 2:- Subtract 15 minutes from a date and recalculate it as follows:

SELECT SUBDATE("2017-06-15 09:34:21", INTERVAL 15 MINUTE);

Result is 2017-06-15 09:19:21

Example 3:- Subtract 3 hours from a date and recalculate it as follows:

SELECT SUBDATE("2017-06-15 09:34:21", INTERVAL 3 HOUR);

Result is 2017-06-15 06:34:21

Example 4:- Return the date after adding two months:

SELECT SUBDATE("2017-06-15", INTERVAL -2 MONTH);

Result is 2017-08-15

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("2017-06-15 10:24:21.000004", "5.000001");

Result is 2017-06-15 10:24:16.000003

Example 2:- Return the datetime by subtracting 3 hours, 2 minutes, and 5.000001 seconds:

SELECT SUBTIME("2017-06-15 10:24:21.000004", "3:2:5.000001");

Result is 2017-06-15 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("2017-08-15 19:30:10");

Result is 19:30:10

Example 3:- From the datetime expression, extract the time component:

SELECT TIME("2017-08-15 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("2017-06-25 13:10:11", "2017-06-15 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("2017-07-23",  "13:10:11");

Result is 2017-07-23 13:10:11

Example 2:- Using the parameters, return a datetime value:

SELECT TIMESTAMP("2017-07-23");

Result is 2017-07-23 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 "0000-00-00").

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("2017-06-20");

Result is 736865

Example 2:- Return the number of days that have passed between the current date and the year zero:

SELECT TO_DAYS("2017-06-20 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:

0 - First day of week is Sunday
1 - First day of week is Monday and the first week of the year has more than 3 days
2 - First day of week is Sunday
3 - First day of week is Monday and the first week of the year has more than 3 days
4 - First day of week is Sunday and the first week of the year has more than 3 days
5 - First day of week is Monday
6 - First day of week is Sunday and the first week of the year has more than 3 days
7 - First day of week is Monday

Technical Details