MySQL Temporal Functions

Overview

In this tutorial, we will learn about MySQL Temporal Functions. Temporal functions work on date and time. They are used to perform operations such as extracting parts of date/time, reformatting , or converting values to seconds or days, etc.

Date and Time can be described in multiple ways. For example, date and/or time can be described in some
of the following ways:

2016-07-15 02:50:15

Friday, July 15,2016

7/15/2016 7:40:25 P.M. EST

etc.

Default Date Formats

The default date components and formats with date and time values are as follows:

Type	Format
DATE	YYYY-MM-DD
TIME	HH:MM:SS
DATETIME	YYYY-MM-DD HH:MI:SS
TIMESTAMP	YYYY-MM-DD HH:MI:SS
YEAR	YYYY

Functions that expect date values usually accept DATETIME values, and ignore the time part. On the other hand, functions that expect time values usually accept DATETIME values and ignore the date part.

MySQL Temporal Functions

Some of the MySQL date and time functions are as follows:

MySQL Temporal Function	Description
NOW()	Current date and time as set on the server host(in DATETIME format)
CURDATE()	Current date as set on the server host(in DATE format)
CURTIME()	Current time as set on the server host(in TIME format)
YEAR(<date_expression>)	Year in YEAR format (4 digits within the 1000-9999 range)
MONTH(<date_expression>)	Month of the year in integer format
DAY(<time_expression>)	Day of the month in integer format
HOUR(<time_expression>)	Hour of the Day in integer format(in 0-23 range)
MINUTE(<time_expression>)	Minute of the Day in integer format
SECOND(<time_expression>)	Second of the Minute in integer format
DAYNAME(<date_expression>)	Day of the week in string format

Examples

mysql> SELECT NOW();
+———————+
| NOW() |
+———————+
| 2022-05-09 22:18:54 |
+———————+
1 row in set (0.00 sec)

mysql> SELECT CURDATE();
+————+
| CURDATE() |
+————+
| 2022-05-09 |
+————+
1 row in set (0.00 sec)

MySQL Temporal Functions

Example of extracting pieces of temporal data:

For example, to extract the current hour from the date and time.

mysql> SELECT HOUR(NOW());
+————-+
| HOUR(NOW()) |
+————-+
| 11 |
+————-+

To extract the day from the current date and time.

mysql> SELECT DAYNAME(NOW());
+—————-+
| DAYNAME(NOW()) |
+—————-+
| Tuesday |
+—————-+

We can use the DATE_FORMAT() to customize the output format of temporal data:

DATE_FORMAT(<date>, <format>)
% required before any predefined date/time format specifiers

mysql> SELECT DATE_FORMAT(NOW(),’%W the %D of %M’);
+————————————–+
| DATE_FORMAT(NOW(),’%W the %D of %M’) |
+————————————–+
| Tuesday the 10th of May |
+————————————–+
1 row in set (0.00 sec)

MySQL DATE_FORMAT Function Example