• TestingDocs
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

MySQL /

How to secure your SQL Database: Tips and Tricks

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

‹ MySQL GROUP BY HAVING clause› MySQL Numeric Functions

Recent Posts

  • How to secure your SQL Database: Tips and Tricks
  • Shaping the Future of Development: Exploring Key Trends in Software Engineering
  • Improving Java Performance with Multithreading
  • Difference between PHP and JavaScript?
  • Bing Conversation Styles
  • ChatGPT Introduction
  • Open Source AI Frameworks
  • Artificial Intelligence Tools
  • Top AI Music Applications
  • Top AI Website Design Tools

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com