MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Funtions and Operators / Question No: 235

What are temporal or date time functions in MySQL?

Answer No: 235

Temporal functions perform operations such as extracting parts of dates and times, reformatting values, or converting values to seconds or days. In many cases, a temporal function that takes a date or time argument also can be given a datetype argument and will ignore the irrelevant part of the datetime value.

There are functions for extracting parts of date or time values:

mysql> SET @d = '2010-04-15', @t = '09:23:57';
mysql> SELECT YEAR(@d), MONTH(@d), DAYOFMONTH(@d);
+----------+-----------+----------------+
| YEAR(@d) | MONTH(@d) | DAYOFMONTH(@d) |
+----------+-----------+----------------+
|     2010 |         4 |             15 |
+----------+-----------+----------------+

mysql> SELECT DAYOFYEAR(@d);
+---------------+
| DAYOFYEAR(@d) |
+---------------+
|           105 |
+---------------+

mysql> SELECT HOUR(@t), MINUTE(@t), SECOND(@t);
+----------+------------+------------+
| HOUR(@t) | MINUTE(@t) | SECOND(@t) |
+----------+------------+------------+
|        9 |         23 |         57 |
+----------+------------+------------+

MAKEDATE() and MAKETIME() compose dates and times from component values. MAKEDATE() produces a date from year and day of year arguments:

mysql> SELECT MAKEDATE(2010,105);
+--------------------+
| MAKEDATE(2010,105) |
+--------------------+
| 2010-04-15         |
+--------------------+

MAKETIME() produces a time from hour, minute, and second arguments.

mysql> SELECT MAKETIME(9,23,57);
+-------------------+
| MAKETIME(9,23,57) |
+-------------------+
| 09:23:57          |
+-------------------+

If you need to determine the current date or time, use CURRENT_DATE or CURRENT_TIME. To get the current date and time as a single value, use CURRENT_TIMESTAMP or NOW():

mysql> SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
+--------------+--------------+---------------------+
| CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP   |
+--------------+--------------+---------------------+
| 2005-05-31   | 21:40:18     | 2005-05-31 21:40:18 |
+--------------+--------------+---------------------+

The three functions in the preceding statement are unlike most functions in that they can be invoked with or without parentheses following the function name.

Related MySQL FAQs to the Above FAQ

What-is-MySQL-date-format-and-its-specifiers What is MySQL date format and its specifiers?

How-to-get-comma-separated-all-values-in-a-single-row-against-each-ID How to get comma separated all values in a single row against each ID?

How-to-concat-values-in-MySQL How to concat values in MySQL?

How-to-convert-time-zone-in-MySQL How to convert time zone in MySQL?

What-are-comparison-functions-in-MySQL What are comparison functions in MySQL

How-to-set-conditions-on-query-data-with-CASE-construct How to set conditions on query data with CASE construct?

How-to-set-control-conditions-on-query-data-with-IF-funtion How to set control conditions on query data with IF funtion?

What-are-mathematical-functions-in-MySQL What are mathematical functions in MySQL?

How-are-aggregate-functions-in-MySQL How are aggregate functions in MySQL?

What-are-string-functions-in-MySQL What are string functions in MySQL?

What-are-NULL-value-related-functions-in-MySQL What are NULL value related functions in MySQL?

How-does-union-work-in-MySQL How does union work in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.