Handling dates and times in SQL queries with MySQL & MariaDB
Manipulating dates in a programming language (PHP, Java ....) is simple and known to all. But what about in the MySQL database? In this article I present what it is possible to do in SQL queries: operation, date interval ... I voluntarily tag this article with PHP although it does not deal with the subject, but I think it will be more useful to developers than to DBAs.
For this article I use a field of type "DATETIME", most of the presented functions are usable on fields of the type "DATE" or "TIME" or with defaults of the analogous functions exist.
This article was originaly written for MySQL, but you can do the same with MariaDB as well.
Extraction in a datetime field
The format of a "DATETIME" field in MySQL is "YYYY-mm-dd hh:ii:ss". It is possible to extract each part of the dates with basic functions DATE(), TIME(), YEAR() ...
SELECT TS, DATE(TS), YEAR(TS), MONTH(TS), DAY(TS), TIME(TS), HOUR(TS), MINUTE(TS), SECOND(TS) FROM MA_TABLE LIMIT 1; +---------------------+------------+----------+-----------+---------+----------+----------+------------+------------+ | TS | DATE(TS) | YEAR(TS) | MONTH(TS) | DAY(TS) | TIME(TS) | HOUR(TS) | MINUTE(TS) | SECOND(TS) | +---------------------+------------+----------+-----------+---------+----------+----------+------------+------------+ | 2012-09-01 16:00:30 | 2012-09-01 | 2012 | 9 | 1 | 16:00:30 | 16 | 0 | 30 | +---------------------+------------+----------+-----------+---------+----------+----------+------------+------------+ 1 row in set (0.00 sec)
These functions are unrestricted and can be used in all parts of an SQL query: SELECT, ON, WHERE, GROUP BY .... One can make other extractions a little more complex with the function EXTRACT() for more particular cases.
SELECT TS, EXTRACT(YEAR_MONTH FROM TS), EXTRACT(DAY_MINUTE FROM TS) FROM MA_TABLE LIMIT 1; +---------------------+-----------------------------+-----------------------------+ | TS | EXTRACT(YEAR_MONTH FROM TS) | EXTRACT(DAY_MINUTE FROM TS) | +---------------------+-----------------------------+-----------------------------+ | 2012-09-01 16:00:30 | 201209 | 11600 | +---------------------+-----------------------------+-----------------------------+ 1 row in set (0.00 sec)
Change date format
It is possible to modify the format of a date with the function DATE_FORMAT().
SELECT TS, DATE_FORMAT(TS, '%Y %M %d'), DATE_FORMAT(TS, '%d-%m-%y %h:%i:%s') FROM MA_TABLE LIMIT 1; +---------------------+-----------------------------+--------------------------------------+ | TS | DATE_FORMAT(TS, '%Y %M %d') | DATE_FORMAT(TS, '%d-%m-%y %h:%i:%s') | +---------------------+-----------------------------+--------------------------------------+ | 2012-09-01 16:30:14 | 2012 September 01 | 01-09-12 04:30:14 | +---------------------+-----------------------------+--------------------------------------+ 1 row in set (0.00 sec)
In general, we always use the same formats to display the dates. As things are well done, MySQL offers formats defined by the accessible function GET_FORMAT().
SELECT TS, DATE_FORMAT(TS, GET_FORMAT(DATE, 'EUR')), DATE_FORMAT(TS, GET_FORMAT(DATE, 'USA')), DATE_FORMAT(TS, GET_FORMAT(DATETIME, 'ISO')) FROM MA_TABLE LIMIT 1; +---------------------+------------------------------------------+------------------------------------------+----------------------------------------------+ | TS | DATE_FORMAT(TS, GET_FORMAT(DATE, 'EUR')) | DATE_FORMAT(TS, GET_FORMAT(DATE, 'USA')) | DATE_FORMAT(TS, GET_FORMAT(DATETIME, 'ISO')) | +---------------------+------------------------------------------+------------------------------------------+----------------------------------------------+ | 2012-09-01 16:30:14 | 01.09.2012 | 09.01.2012 | 2012-09-01 16:30:14 | +---------------------+------------------------------------------+------------------------------------------+----------------------------------------------+
The first parameter of the function GET_FORMAT() must be DATE, DATETIME or TIME and lets you define which part you want to display.
Operation on a date
Arithmetic operations are of course possible. Several functions and notations give the same result. If I want to add 5 days to a date, I can use ADDDATE(), DATE_ADD() or neither.
SELECT TS, ADDDATE(TS, 5), ADDDATE(TS, INTERVAL 5 DAY), DATE_ADD(TS, INTERVAL 5 DAY), TS + INTERVAL 5 DAY FROM MA_TABLE LIMIT 1; +---------------------+---------------------+-----------------------------+------------------------------+---------------------+ | TS | ADDDATE(TS, 5) | ADDDATE(TS, INTERVAL 5 DAY) | DATE_ADD(TS, INTERVAL 5 DAY) | TS + INTERVAL 5 DAY | +---------------------+---------------------+-----------------------------+------------------------------+---------------------+ | 2012-09-02 16:00:23 | 2012-09-07 16:00:23 | 2012-09-07 16:00:23 | 2012-09-07 16:00:23 | 2012-09-07 16:00:23 | +---------------------+---------------------+-----------------------------+------------------------------+---------------------+ 1 row in set (0.00 sec)
The first notation of the ADDDATE() function only allows you to add a number of days. The use of the key word "INTERVAL", in the other notations, makes it possible to create more complex intervals of time.
SELECT TS, TS + INTERVAL '1 10' YEAR_MONTH, TS + INTERVAL 1 MONTH + INTERVAL 10 DAY FROM MA_TABLE LIMIT 1; +---------------------+---------------------------------+-----------------------------------------+ | TS | TS + INTERVAL '1 10' YEAR_MONTH | TS + INTERVAL 1 MONTH + INTERVAL 10 DAY | +---------------------+---------------------------------+-----------------------------------------+ | 2012-09-02 16:30:33 | 2014-07-02 16:30:33 | 2012-10-12 16:30:33 | +---------------------+---------------------------------+-----------------------------------------+ 1 row in set (0.00 sec)
Of course we can do the same thing for a subtraction with the functions SUBDATE(), DATE_SUB() or by specifying a negative value.
Finally, it is possible to calculate the difference between two dates or two hours with the DATEDIFF() and TIMEDIFF() functions. DATEDIFF() returns only a number of days. Both functions only work on their respective parts.
SELECT DATEDIFF('2012-12-31 12:36:00', '2012-12-25 23:59:00'), TIMEDIFF('2012-12-31 12:36:00', '2012-12-25 23:59:00'); +--------------------------------------------------------+--------------------------------------------------------+ | DATEDIFF('2012-12-31 12:36:00', '2012-12-25 23:59:00') | TIMEDIFF('2012-12-31 12:36:00', '2012-12-25 23:59:00') | +--------------------------------------------------------+--------------------------------------------------------+ | 6 | 132:37:00 | +--------------------------------------------------------+--------------------------------------------------------+ 1 row in set (0.00 sec)
To obtain the difference in other units it is necessary to use the function TIMESTAMPDIFF(). Beware to the parameters, the order of the two dates is reversed compared to the previous functions.
SELECT TIMESTAMPDIFF(MINUTE, '2012-12-25 23:59:00', '2012-12-31 12:36:00'); +---------------------------------------------------------------------+ | TIMESTAMPDIFF(MINUTE, '2012-12-25 23:59:00', '2012-12-31 12:36:00') | +---------------------------------------------------------------------+ | 7957 | +---------------------------------------------------------------------+ 1 row in set (0.00 sec)
Convert a date
There is a whole range of functions to convert dates:
- TO_DAYS() : get number of days since year 0
- TIME_TO_SEC(): convert hour in seconds
- DAYOFYEAR(): get day of the year
And if the date is stored in a field other than "DATE", "DATETIME" or "TIME" it is still possible to use the CAST() function to use the many date manipulation functions.
SELECT CAST('2012-12-25' AS DATETIME); +--------------------------------+ | CAST('2012-12-25' AS DATETIME) | +--------------------------------+ | 2012-12-25 00:00:00 | +--------------------------------+ 1 row in set (0.00 sec)
And if the date is in a different format, it is possible to use the function STR_TO_DATE(), which indicates to MySQL the format of the date passed in parameter.
SELECT STR_TO_DATE('25/12/2012', '%d/%m/%Y'), CAST('25/12/2012' AS DATE); +---------------------------------------+----------------------------+ | STR_TO_DATE('25/12/2012', '%d/%m/%Y') | CAST('25/12/2012' AS DATE) | +---------------------------------------+----------------------------+ | 2012-12-25 | NULL | +---------------------------------------+----------------------------+ 1 row in set, 1 warning (0.00 sec)
I hope that, with this article, you have discovered or rediscovred very useful functions in SQL language. You can find the documentation here.
Add a comment