MySQL Date Functions

MySQL provides various date and Time functions to work with Dates such as Finding current date, Time, format date, extract date part, time part etc.

MySQL Date Functions

The following are the list of MySQL Date functions that are available to work with Date and time.

You can click each function to see details about the function with multiple examples. Or, scroll down to see an example of each function.

Temporal Intervals of Date and Time Functions in MySQL

The following are the list of available temporal interval expressions, and unit values.

MySQL Date and Time Functions Examples

The following examples helps you to understand these Date and Time functions in MySQL

MySQL Date Functions Example 1

In this example, we are going to use the ADDDATE and ADDTIME functions to add the given values. Next, we used CONVERT_TZ to convert the date from one standard to another.

SELECT ADDDATE('2016-01-02', INTERVAL 2 MONTH), ADDDATE('2018-02-01', INTERVAL 31 DAY);

SELECT ADDTIME('2018-12-31 23:55:57', '2 2:22:22');

SELECT CONVERT_TZ('2018-12-31 23:59:59', '+00:00', '-05:30');

OUTPUT

MySQL Date Functions Example 2

In this example, we are going to use the CURDATE(), CURRENT_DATE, CURRENT_DATE() functions to find the current date.

SELECT CURDATE(), CURDATE() + 5;

SELECT CURRENT_DATE, CURRENT_DATE + 2;

SELECT CURRENT_DATE(), CURRENT_DATE() + 10;

OUTPUT

MySQL Time Functions Example 3

In this example, we are going to use the CURTIME, CURRENT_TIME(), CURRENT_TIME functions to find the current time.

SELECT CURTIME(), CURTIME() + 1;

SELECT CURRENT_TIME(), CURRENT_TIME() + 10;

SELECT CURRENT_TIME, CURRENT_TIME + 2;

OUTPUT

MySQL Date Functions Example 4

In this example, we are going to use the DATE function to get date, and DATEDIFF function to find the difference between two dates. Next, we used the DATE_ADD function to add date intervals.

SELECT DATE('2018-12-31 12:59:59'), DATE('2012-11-18 11:22:33');

SELECT DATEDIFF('2018-12-31', '2019-02-03'), DATEDIFF('2018-12-01', '2018-02-01'); 

SELECT DATE_ADD('2018-12-31', INTERVAL 2 DAY), DATE_ADD('2018-12-31', INTERVAL 2 YEAR);

OUTPUT

MySQL Date Functions Example 5

In this example, we are going to use the DATE_FORMAT function to format the given Date. DATE_SUB function is to subtract the intervals from date. Next, we used DAY function to return day value

SELECT DATE_FORMAT('2018-12-31 23:59:59', '%W, %D %M %Y');

SELECT DATE_SUB('2018-12-31', INTERVAL 12 DAY), DATE_SUB('2018-12-31', INTERVAL 2 YEAR);

SELECT DAY('2018-12-31'), DAY('2018-01-01');

OUTPUT

MySQL Date Functions Example 6

In this example, we are going to use the DAYNAME and DAYOFMONTH functions to get name of the day, Day number of Month. Next, we used DAYOFWEEK to get the weekday index number

SELECT DAYNAME('2018-12-31'), DAYNAME('2019-01-01');

SELECT DAYOFMONTH('2018-12-14'), DAYOFMONTH('2019-01-31');

SELECT DAYOFWEEK('2018-12-14'), DAYOFWEEK('2019-01-31');

OUTPUT

MySQL Date Functions Example 7

Here, we use the EXTRACT function to extract date parts from date. FROM_DAYS function to return date from given days. FROM_UNIXTIME function to format unix timestamp as date and time.

SELECT EXTRACT(YEAR FROM '2019-07-02'), EXTRACT(MONTH FROM '2018-12-31');

SELECT FROM_DAYS(100000), FROM_DAYS(950010);

SELECT FROM_UNIXTIME(122227899), FROM_UNIXTIME(1233456789);

OUTPUT

MySQL Date Functions Example 8

In this example, we are going to use the HOUR function to get hour value, LAST_DAY function to get last day in a month. Here, GET_FORMAT has to use along with DATE_FORMAT or STR_TO_DATE.

SELECT DATE_FORMAT('2018-12-31', GET_FORMAT(DATE, 'EUR'));

SELECT HOUR('12:22:33'), HOUR('23:59:59'), HOUR('2019-01-02 17:12:13');

SELECT LAST_DAY('2018-04-12'), LAST_DAY('2018-02-05');

OUTPUT

MySQL Date Functions Example 9

In this example, we are going to use the LOCALTIME, LOCALTIME(), and LOCALTIMESTAMP functions to get the current date and time.

SELECT LOCALTIME, LOCALTIME + 1;

SELECT LOCALTIME(), LOCALTIME() + 15;

SELECT LOCALTIMESTAMP, LOCALTIMESTAMP + 4;

OUTPUT

MySQL Date Functions Example 10

In this example, we used the LOCALTIMESTAMP(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP() functions to get the current Date and Time.

SELECT LOCALTIMESTAMP(), LOCALTIMESTAMP() + 10;

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + 10;

SELECT CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP() + 10;

OUTPUT

MySQL Date Functions Example 11

In this example, we used the MAKEDATE function to create date from Year and Day of Year, MAKETIME to create time from Hour, Minute and Second. Next, we are extracting or returning Microsecond value using MICROSECOND function.

SELECT MAKEDATE(2018, 28), MAKEDATE(2018, 364);

SELECT MAKETIME(10, 11, 22), MAKETIME(23, 59, 58);

SELECT MICROSECOND('10:22:33.324567'), MICROSECOND('19:11:22.112233');

OUTPUT

MySQL Date Functions Example 12

Here, we are going to use the MINUTE function to get the Minute value. Next, we used MONTH and MONTHNAME functions to get the Month Number and Month Name (name of the month).

SELECT MINUTE('10:22:33.324567'), MINUTE('2018-02-04 19:11:22');

SELECT MONTH('2018-12-31'), MONTH('2018-02-04 19:11:22');

SELECT MONTHNAME('2018-12-31'), MONTHNAME('2018-01-04 19:11:22');

OUTPUT

MySQL Date Functions Example 13

In this example, we are going to use the NOW function to get the current date and time. Next, we used PERIOD_ADD and PERIOD_DIFF to add months, and to to find the difference between two periods.

SELECT NOW(), NOW() + 5;

SELECT PERIOD_ADD(201801, 3), PERIOD_ADD(201802, 10);

SELECT PERIOD_DIFF(201812, 201907), PERIOD_DIFF(201812, 201802);

OUTPUT

MySQL Date Functions Example 14

In this example, we are going to use the SECOND function to get seconds, SEC_TO_TIME function to convert the given second to time. Next, we used STR_TO_DATE to convert string to date.

SELECT SECOND('10:22:33.324567'), SECOND('2018-02-04 19:11:22');

SELECT SEC_TO_TIME(3652), SEC_TO_TIME(9875);

SELECT STR_TO_DATE('April 1, 2018', '%M %d, %Y'); 

OUTPUT

MySQL Date Functions Example 15

In this example, we are going to use the SUBDATE and SUBTIME functions to subtract date or time intervals from the given date and time. Next, we sued the SYSDATE function to return the System date and time

SELECT SUBDATE('2018-12-31', INTERVAL 15 DAY), SUBDATE('2018-12-31', INTERVAL 2 YEAR);

SELECT SUBTIME('2018-12-31 10:11:12', '3 13:48:47');

SELECT SYSDATE(), SYSDATE() + 2;

OUTPUT

MySQL Time Functions Example 16

In this example, we are going to use the TIME function to get Time, TIMEDIFF to find the difference between two time values. Next, we used the TIMESTAMP to return the Date and Time

SELECT TIME('2018-01-31 10:22:33.324567'), TIME('2018-02-04 19:11:22');

SELECT TIMEDIFF('2018-12-31 23:59:59.111222', '2018-12-30 01:10:10.333444');

SELECT TIMESTAMP('2018-12-31'), TIMESTAMP('2018-12-31 23:59:59');

OUTPUT

MySQL Time Functions Example 17

In this example, we are going to use the TIMESTAMPADD function to add intervals to date, and TIMESTAMPDIFF to find the difference between two time stamps. Next, we used the the TIME_FORMAT function to format the given time.

SELECT TIMESTAMPADD(HOUR, 13, '2018-01-31 10:22:33'), TIMESTAMPADD(MINUTE, 48, '2018-02-04 19:11:22');

SELECT TIMESTAMPDIFF(MONTH, '2018-01-31', '2018-12-30'), TIMESTAMPDIFF(MINUTE,'2018-02-04', '2018-02-04 19:11:22');

SELECT TIME_FORMAT('4:12:13', '%k %i %S');

OUTPUT

MySQL Date Functions Example 18

In this example, we are going to use the TIME_TO_SECONDS to convert given time to number of seconds. Next, we used TO_DAYS function to retune days

SELECT TIME_FORMAT('4:12:13', '%k %i %S');

SELECT TIME_TO_SEC('23:59:59'), TIME_TO_SEC('10:10:00');

SELECT TO_DAYS('1947-08-15'), TO_DAYS('2018-12-31');

OUTPUT

MySQL Date Functions Example 19

Here, we used the TO_SECONDS function to convert date to seconds, UNIX_TIMESTAMP to return the unix time stamp. Next, we used UTC_DATE to get the UTC date value.

SELECT TO_SECONDS('1947-08-15'), TO_SECONDS('2018-12-31');

SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP('2018-12-31 23:59:59');

SELECT UTC_DATE, UTC_DATE();

OUTPUT

MySQL Time Functions Example 20

In this example, we are going to use the UTC_TIME to get Time, and UTC_TIMESTAMP to find the UTC Date and Time. Next, we used WEEK function to get the Week number.

SELECT UTC_TIME, UTC_TIME();

SELECT UTC_TIMESTAMP, UTC_TIMESTAMP();

SELECT WEEK('2018-12-31'), WEEK('2018-12-31', 1);

OUTPUT

MySQL Date Functions Example 21

In this example, we are going to use the WEEKDAY function to get index number, WEEKOFYEAR function to find the calendar week. Next, we used the YEAR function to get the year value from given date.

SELECT WEEKDAY('2018-03-31'), WEEKDAY('2018-12-04 23:59:59');

SELECT WEEKOFYEAR('2018-12-31'), WEEKOFYEAR('2018-12-04 23:59:59');

SELECT YEAR('1947-08-15'), YEAR('2018-12-31');

OUTPUT

MySQL Date Functions Example 22

In this example, we use the YEARWEEK function to return the year and week from given date.

SELECT YEARWEEK('1947-08-15'), YEARWEEK('2018-12-31');

OUTPUT

Thank You for Visiting Our Blog