TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

MySQL

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

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/

For more information on MySQL Database:

https://www.mysql.com/

Related Posts

DataGrip Desktop Shortcut

MySQL /

Launch DataGrip on Windows

DataGrip Download

MySQL /

Install DataGrip IDE on Windows 11

MySQL Workbench Windows 11

MySQL /

New MySQL Connection in Workbench

MySQL Command-line Client

MySQL /

Command-Line MySQL Client

Start MySQL Client

MySQL /

Start MySQL Client on Windows 11

‹ MySQL GROUP BY HAVING clause› MySQL Numeric Functions

Recent Posts

  • Update draw.io on Windows
  • Install RAPTOR Avalonia on CentOS
  • Download RAPTOR Avalonia Edition on Windows
  • npm doctor command
  • Build & Run CLion Project
  • Create New CLion C Project on Windows
  • Configure CLion Toolchains on Windows
  • Launch CLion IDE on Windows
  • Activate CLion IDE
  • CLion IDE for C/C++ Development

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com