Handling dates and times in SQL queries with MySQL & MariaDB
Manipulating dates in a programming language (PHP, Java...) is well known. But what can you do directly in MySQL or MariaDB? This article lists the date and time functions you will use in SQL queries: get the current date, extract a part, format, add an interval, compute a difference, convert, and filter on a date range without breaking the index.
All examples use a DATETIME column named TS. Most functions also work with DATE and TIME columns.
Tested with MySQL 8.4 LTS, MySQL 9.7 LTS and MariaDB 11.8 LTS (November 2026): every query below returns the same result on all three, unless stated otherwise.
TL;DR: NOW() and UTC_TIMESTAMP() give the current time; YEAR(), EXTRACT(), WEEK() and LAST_DAY() extract part of a date;
DATE_FORMAT() formats it; DATE_ADD(), DATE_SUB() and INTERVAL add or subtract; DATEDIFF() and TIMESTAMPDIFF() compute differences;
STR_TO_DATE(), UNIX_TIMESTAMP() and CONVERT_TZ() convert. To filter a period, use >= and < on the raw column.
Get the current date and time
NOW() returns the date and time in the session time zone, CURDATE() and CURTIME() return only one part, and UTC_TIMESTAMP() returns the time in UTC whatever the server configuration.
SET time_zone = 'Europe/Paris'; SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP(); +---------------------+------------+-----------+---------------------+ | NOW() | CURDATE() | CURTIME() | UTC_TIMESTAMP() | +---------------------+------------+-----------+---------------------+ | 2026-11-07 18:27:24 | 2026-11-07 | 18:27:24 | 2026-11-07 17:27:24 | +---------------------+------------+-----------+---------------------+
NOW() is fixed at the start of the statement, SYSDATE() returns the time at which it is executed. The difference only shows on long queries, but it matters for replication and for queries that must be reproducible: prefer NOW().
SELECT NOW(), SLEEP(2), NOW(), SYSDATE(); +---------------------+----------+---------------------+---------------------+ | NOW() | SLEEP(2) | NOW() | SYSDATE() | +---------------------+----------+---------------------+---------------------+ | 2026-11-07 18:27:24 | 0 | 2026-11-07 18:27:24 | 2026-11-07 18:27:26 | +---------------------+----------+---------------------+---------------------+
Extract part of a date
The format of a DATETIME is YYYY-MM-DD hh:mm:ss. Each part can be extracted with a dedicated function: 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 | +---------------------+------------+----------+-----------+---------+----------+----------+------------+------------+
These functions can be used in every part of a query: SELECT, ON, WHERE, GROUP BY... For combined parts, use EXTRACT().
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 | +---------------------+-----------------------------+-----------------------------+
Calendar functions answer the usual reporting questions: day of the week, week number, quarter, last day of the month.
SELECT TS, DAYNAME(TS), DAYOFWEEK(TS), WEEKDAY(TS), WEEK(TS), WEEK(TS, 3), QUARTER(TS), LAST_DAY(TS) FROM MA_TABLE LIMIT 1; +---------------------+-------------+---------------+-------------+----------+-------------+-------------+--------------+ | TS | DAYNAME(TS) | DAYOFWEEK(TS) | WEEKDAY(TS) | WEEK(TS) | WEEK(TS, 3) | QUARTER(TS) | LAST_DAY(TS) | +---------------------+-------------+---------------+-------------+----------+-------------+-------------+--------------+ | 2012-09-01 16:00:30 | Saturday | 7 | 5 | 35 | 35 | 3 | 2012-09-30 | +---------------------+-------------+---------------+-------------+----------+-------------+-------------+--------------+
Watch the numbering: DAYOFWEEK() starts at 1 on Sunday, WEEKDAY() starts at 0 on Monday. WEEK() depends on a mode: without it, weeks start on Sunday; WEEK(TS, 3) gives the ISO 8601 week number used in Europe. Here both give 35, but they differ around New Year: for 2027-01-01, WEEK() returns 0 and WEEK(TS, 3) returns 53.
Format a date
DATE_FORMAT() displays a date in any 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 | +---------------------+-----------------------------+--------------------------------------+
Note the 04: %h is the hour on 12 hours, use %H for 24 hours. The most useful specifiers:
| Specifier | Meaning | Example |
|---|---|---|
%Y / %y | Year, 4 or 2 digits | 2012 / 12 |
%m / %c | Month, with or without leading zero | 09 / 9 |
%M / %b | Month name, full or short | September / Sep |
%d / %e | Day of the month, with or without leading zero | 01 / 1 |
%W / %a | Day name, full or short | Saturday / Sat |
%H / %h | Hour, 24 or 12 hours | 16 / 04 |
%i / %s | Minutes / seconds | 30 / 14 |
%p | AM or PM | PM |
%v | ISO 8601 week number, same as WEEK(TS, 3) | 35 |
Day and month names follow the lc_time_names variable (SET lc_time_names = 'fr_FR'; to get them in French). For common formats, GET_FORMAT() returns a ready-made pattern.
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 GET_FORMAT() is DATE, DATETIME or TIME, the second one 'EUR', 'USA', 'JIS', 'ISO' or 'INTERNAL'.
Add or subtract an interval
Several notations give the same result. To add 5 days: ADDDATE(), DATE_ADD() or the + INTERVAL operator.
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 | +---------------------+---------------------+-----------------------------+------------------------------+---------------------+
The short form ADDDATE(TS, 5) only adds days. With INTERVAL, you can use any unit, and combine them.
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 | +---------------------+---------------------------------+-----------------------------------------+
| Unit | Value format | Example |
|---|---|---|
MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR | Integer | INTERVAL 3 WEEK |
YEAR_MONTH | 'Y M' | INTERVAL '1 10' YEAR_MONTH |
DAY_HOUR | 'D H' | INTERVAL '2 12' DAY_HOUR |
DAY_MINUTE | 'D H:M' | INTERVAL '1 02:30' DAY_MINUTE |
HOUR_MINUTE | 'H:M' | INTERVAL '02:30' HOUR_MINUTE |
MINUTE_SECOND | 'M:S' | INTERVAL '10:30' MINUTE_SECOND |
To subtract, use SUBDATE(), DATE_SUB(), the - INTERVAL operator or a negative value.
SELECT TS, DATE_SUB(TS, INTERVAL 2 HOUR), SUBDATE(TS, 10), TS - INTERVAL 1 WEEK FROM MA_TABLE LIMIT 1; +---------------------+-------------------------------+---------------------+----------------------+ | TS | DATE_SUB(TS, INTERVAL 2 HOUR) | SUBDATE(TS, 10) | TS - INTERVAL 1 WEEK | +---------------------+-------------------------------+---------------------+----------------------+ | 2012-09-01 16:00:30 | 2012-09-01 14:00:30 | 2012-08-22 16:00:30 | 2012-08-25 16:00:30 | +---------------------+-------------------------------+---------------------+----------------------+
Be careful with months: when the target day does not exist, MySQL and MariaDB return the last day of the month instead of overflowing into the next one.
SELECT '2024-01-31' + INTERVAL 1 MONTH, '2023-01-31' + INTERVAL 1 MONTH, '2024-02-29' + INTERVAL 1 YEAR; +---------------------------------+---------------------------------+--------------------------------+ | '2024-01-31' + INTERVAL 1 MONTH | '2023-01-31' + INTERVAL 1 MONTH | '2024-02-29' + INTERVAL 1 YEAR | +---------------------------------+---------------------------------+--------------------------------+ | 2024-02-29 | 2023-02-28 | 2025-02-28 | +---------------------------------+---------------------------------+--------------------------------+
Difference between two dates
DATEDIFF() returns a number of days, TIMEDIFF() a duration.
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 | +--------------------------------------------------------+--------------------------------------------------------+
For any other unit, use TIMESTAMPDIFF(). Beware, the order of the dates is reversed compared to DATEDIFF(): the oldest date comes first.
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 | +---------------------------------------------------------------------+
DATEDIFF() ignores the time: it counts calendar days. TIMESTAMPDIFF(DAY, ...) counts full 24-hour periods. One hour apart across midnight, the results differ:
SELECT DATEDIFF('2024-01-02 00:30:00', '2024-01-01 23:30:00'), TIMESTAMPDIFF(DAY, '2024-01-01 23:30:00', '2024-01-02 00:30:00'); +--------------------------------------------------------+------------------------------------------------------------------+ | DATEDIFF('2024-01-02 00:30:00', '2024-01-01 23:30:00') | TIMESTAMPDIFF(DAY, '2024-01-01 23:30:00', '2024-01-02 00:30:00') | +--------------------------------------------------------+------------------------------------------------------------------+ | 1 | 0 | +--------------------------------------------------------+------------------------------------------------------------------+
Convert a date
Some functions convert a date into a number: TO_DAYS() (days since year 0), TIME_TO_SEC() (seconds since midnight), DAYOFYEAR() (day of the year).
SELECT TS, TO_DAYS(TS), TIME_TO_SEC(TS), DAYOFYEAR(TS) FROM MA_TABLE LIMIT 1; +---------------------+-------------+-----------------+---------------+ | TS | TO_DAYS(TS) | TIME_TO_SEC(TS) | DAYOFYEAR(TS) | +---------------------+-------------+-----------------+---------------+ | 2012-09-01 16:00:30 | 735112 | 57630 | 245 | +---------------------+-------------+-----------------+---------------+
If the date is stored in a column that is not DATE, DATETIME or TIME, CAST() converts it so you can use all the functions above.
SELECT CAST('2012-12-25' AS DATETIME); +--------------------------------+ | CAST('2012-12-25' AS DATETIME) | +--------------------------------+ | 2012-12-25 00:00:00 | +--------------------------------+
If the date is in another format, CAST() returns NULL with a warning. STR_TO_DATE() takes the format as a parameter, with the same specifiers as DATE_FORMAT().
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 | +---------------------------------------+----------------------------+
To exchange dates with an application, UNIX_TIMESTAMP() and FROM_UNIXTIME() convert to and from a Unix timestamp. Both use the session time zone, here UTC.
SET time_zone = '+00:00'; SELECT UNIX_TIMESTAMP('2012-09-01 16:00:30'), FROM_UNIXTIME(1346515230), FROM_UNIXTIME(1346515230, '%d/%m/%Y'); +---------------------------------------+---------------------------+---------------------------------------+ | UNIX_TIMESTAMP('2012-09-01 16:00:30') | FROM_UNIXTIME(1346515230) | FROM_UNIXTIME(1346515230, '%d/%m/%Y') | +---------------------------------------+---------------------------+---------------------------------------+ | 1346515230 | 2012-09-01 16:00:30 | 01/09/2012 | +---------------------------------------+---------------------------+---------------------------------------+
Finally, CONVERT_TZ() converts a date from one time zone to another. Offsets ('+02:00') always work; named zones ('Europe/Paris') require the time zone tables to be loaded with mysql_tzinfo_to_sql, otherwise the function returns NULL. Named zones handle daylight saving time, offsets don't.
SELECT CONVERT_TZ('2012-09-01 16:00:30', '+00:00', '+02:00'), CONVERT_TZ('2012-09-01 16:00:30', 'UTC', 'Europe/Paris'); +-------------------------------------------------------+----------------------------------------------------------+ | CONVERT_TZ('2012-09-01 16:00:30', '+00:00', '+02:00') | CONVERT_TZ('2012-09-01 16:00:30', 'UTC', 'Europe/Paris') | +-------------------------------------------------------+----------------------------------------------------------+ | 2012-09-01 18:00:30 | 2012-09-01 18:00:30 | +-------------------------------------------------------+----------------------------------------------------------+
Filter on a period without breaking the index
To select the rows of a year or a month, the reflex is WHERE YEAR(created_at) = 2024. It returns the right rows, but on MySQL the function applied to the column prevents the use of the index: EXPLAIN shows a full index scan (type: index) instead of a range. MariaDB, since version 11.1, rewrites this condition by itself and uses the index. To be efficient everywhere, compare the raw column with an upper bound excluded:
-- slow on MySQL: the index on created_at is scanned entirely SELECT id FROM orders WHERE YEAR(created_at) = 2024; -- uses the index (type: range) on MySQL and MariaDB SELECT id FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
Avoid BETWEEN '2024-01-01' AND '2024-12-31' on a DATETIME column: the upper bound means 2024-12-31 00:00:00, so the whole last day is forgotten. On a test table with a row at 2024-12-31 23:59:59, BETWEEN returns 2 rows where >= ... < returns 3. To read an EXPLAIN, see how to use EXPLAIN to optimize a query.
FAQ
How do I get yesterday's date in MySQL?
CURDATE() - INTERVAL 1 DAY for the date, NOW() - INTERVAL 1 DAY for the date and time.
How do I get the first and last day of the month?
LAST_DAY(TS) gives the last day. For the first day: LAST_DAY(TS - INTERVAL 1 MONTH) + INTERVAL 1 DAY, or DATE_FORMAT(TS, '%Y-%m-01').
How do I compute an age in years?
TIMESTAMPDIFF(YEAR, birth_date, CURDATE()): it counts full years, so the birthday must have passed.
Should I store dates in DATETIME, TIMESTAMP or INT?
It depends on time zones and on the range of dates you need. I compare the three options in which format to use to store dates with MySQL / MariaDB. On the application side, see dates and times in PHP with DateTime.
Official documentation: MySQL 8.4 date and time functions and MariaDB date and time functions.
Add a comment