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)
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 Tutorials
MySQL Tutorials on this website:
https://www.testingdocs.com/mysql-tutorials-for-beginners/
For more information on MySQL Database: