Date and Time Functions

MySQL Tutorials - Herong's Tutorial Examples

∟Date and Time Functions

Describes some commonly used date and time functions like, CURRENT_DATE(), CURRENT_TIME(), and EXTRACT().

MySQL supports a number of built-in functions that allows you to manipulate dates and times. Some commonly used date and time functions are listed below.

CURRENT_DATE() - Returns the current date.
CURRENT_TIME() - Returns the current time.
CURRENT_TIMESTAMP() - Returns the current date and time.
DATE(date_string) - Returns the date by parsing a given date string in several commonly used date formats.
EXTRACT(field_name FROM date_time) - Returns a numeric value of the specified field name of the specified date and time value.
MAKEDATE(year, dayofyear) - Returns the date of the specified year and day of the year.
MAKETIME(hour, minute, second) - Returns the time of the specified hour, minute and second.
DATE(time_string) - Returns the time by parsing a given time string in several commonly used time and datetime formats.

Examples of using date and time functions, DateTimeFunctions.sql:

-- DateTimeFunctions.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
SELECT CURRENT_TIME();
SELECT EXTRACT(HOUR FROM CURRENT_TIME());
SELECT EXTRACT(MONTH FROM TIMESTAMP'1999-02-28 10:20:30');
SELECT DATE('2023-2-3 01:02:03');
SELECT DATE('2023/2/3 01:02:03');
SELECT DATE(CONCAT_WS('-', 1, 2, 3));
SELECT MAKEDATE(2014, 365);
SELECT MAKETIME(12, 15, 30);
SELECT TIME('2003-12-31 01:02:03.000123');

Output:

CURRENT_TIME()
21:38:37
EXTRACT(HOUR FROM CURRENT_TIME())
21
EXTRACT(MONTH FROM TIMESTAMP'1999-02-28 10:20:30')
2
SELECT DATE('2023-2-3 01:02:03');
'2023-2-3'
SELECT DATE('2023/2/3');
'2023-2-3'
SELECT DATE(CONCAT_WS('-', 1, 2, 3));
'0001-02-03'
SELECT MAKEDATE(2014, 365);
'2014-12-31'
SELECT MAKETIME(12, 15, 30)
'12:15:30'
SELECT TIME('2003-12-31 01:02:03.000123');
'01:02:03.000123'

By the way, there date function to return the date of a given year, month and day of the month. The best way is use DATE(CONCAT_WS('-', year, month, day)) as shown in the above example.