Date & time functions

NOW, CURDATE, DATE_FORMAT, DATE_ADD, DATEDIFF, TIMESTAMPDIFF, EXTRACT and time zones.

Almost every application stores dates: sign-ups, orders, deadlines, logins. MySQL has a rich set of functions to get the current time, pull dates apart, format them, do date arithmetic and convert between time zones. (The date types themselves were covered in Data types.)

Sample data#

SQL

CREATE TABLE orders (
    id         INT PRIMARY KEY,
    customer   VARCHAR(30) NOT NULL,
    ordered_at DATETIME NOT NULL,
    shipped_on DATE
);

INSERT INTO orders VALUES
    (1, 'Asha',  '2026-01-15 09:30:00', '2026-01-17'),
    (2, 'Rohan', '2026-02-28 22:10:00', '2026-03-03'),
    (3, 'Meera', '2026-03-01 08:00:00', NULL),
    (4, 'Asha',  '2026-09-29 18:45:00', '2026-09-30');

The current date and time#

SQL

SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();

Output

+---------------------+------------+-----------+---------------------+
| NOW()               | CURDATE()  | CURTIME() | UTC_TIMESTAMP()     |
+---------------------+------------+-----------+---------------------+
| 2026-09-30 15:42:10 | 2026-09-30 | 15:42:10  | 2026-09-30 10:12:10 |
+---------------------+------------+-----------+---------------------+

(Your values will differ, of course.) NOW() and CURRENT_TIMESTAMP are the same thing and use the session time zone. NOW() returns the time the statement started, so it's the same in every row of a query. SYSDATE() returns the exact moment it's evaluated. NOW(3) adds milliseconds.

Extracting parts of a date#

SQL

SELECT id, ordered_at,
       YEAR(ordered_at)       AS y,
       MONTH(ordered_at)      AS m,
       DAY(ordered_at)        AS d,
       DAYNAME(ordered_at)    AS weekday,
       HOUR(ordered_at)       AS h,
       QUARTER(ordered_at)    AS q,
       DATE(ordered_at)       AS just_date
FROM orders;

Output

+----+---------------------+------+------+------+----------+------+------+------------+
| id | ordered_at          | y    | m    | d    | weekday  | h    | q    | just_date  |
+----+---------------------+------+------+------+----------+------+------+------------+
|  1 | 2026-01-15 09:30:00 | 2026 |    1 |   15 | Thursday |    9 |    1 | 2026-01-15 |
|  2 | 2026-02-28 22:10:00 | 2026 |    2 |   28 | Saturday |   22 |    1 | 2026-02-28 |
|  3 | 2026-03-01 08:00:00 | 2026 |    3 |    1 | Sunday   |    8 |    1 | 2026-03-01 |
|  4 | 2026-09-29 18:45:00 | 2026 |    9 |   29 | Tuesday  |   18 |    3 | 2026-09-29 |
+----+---------------------+------+------+------+----------+------+------+------------+

Others you'll meet: MONTHNAME(), WEEKDAY() (Monday = 0), DAYOFWEEK() (Sunday = 1), DAYOFYEAR(), WEEK(), LAST_DAY() (last day of that month) and EXTRACT(unit FROM d), the standard-SQL form, e.g. EXTRACT(YEAR_MONTH FROM ordered_at) → 202601.

Formatting dates with DATE_FORMAT#

SQL

SELECT ordered_at,
       DATE_FORMAT(ordered_at, '%e %b %Y')           AS short_uk,
       DATE_FORMAT(ordered_at, '%W, %M %D')          AS long_text,
       DATE_FORMAT(ordered_at, '%d/%m/%Y %h:%i %p')  AS india_12h
FROM orders WHERE id IN (1, 2);

Output

+---------------------+-------------+-------------------------+---------------------+
| ordered_at          | short_uk    | long_text               | india_12h           |
+---------------------+-------------+-------------------------+---------------------+
| 2026-01-15 09:30:00 | 15 Jan 2026 | Thursday, January 15th  | 15/01/2026 09:30 AM |
| 2026-02-28 22:10:00 | 28 Feb 2026 | Saturday, February 28th | 28/02/2026 10:10 PM |
+---------------------+-------------+-------------------------+---------------------+

Specifier	Meaning	Example
`%Y` / `%y`	4- / 2-digit year	2026 / 26
`%m` / `%c`	month number, padded / not padded	01 / 1
`%M` / `%b`	month name / abbreviated	January / Jan
`%d` / `%e`	day of month, padded / not padded	05 / 5
`%D`	day with suffix	15th
`%W` / `%a`	weekday name / abbreviated	Thursday / Thu
`%H` / `%h`	hour 00–23 / 01–12	22 / 10
`%i` / `%s`	minutes / seconds	10 / 00
`%p`	AM or PM	PM

The reverse is STR_TO_DATE, which parses text in a known format. It's very useful when importing CSV files:

SQL

SELECT STR_TO_DATE('30/09/2026', '%d/%m/%Y') AS parsed;

Output

+------------+
| parsed     |
+------------+
| 2026-09-30 |
+------------+

Date arithmetic#

Add or subtract an interval with DATE_ADD/DATE_SUB or with +/-:

SQL

SELECT
    DATE_ADD('2026-01-31', INTERVAL 1 MONTH)     AS plus_month,
    '2026-09-30' + INTERVAL 10 DAY               AS plus_10d,
    DATE_SUB('2026-03-01', INTERVAL 1 DAY)       AS day_before,
    '2026-09-30 23:30:00' + INTERVAL 45 MINUTE   AS later;

Output

+------------+------------+------------+---------------------+
| plus_month | plus_10d   | day_before | later               |
+------------+------------+------------+---------------------+
| 2026-02-28 | 2026-10-10 | 2026-02-28 | 2026-10-01 00:15:00 |
+------------+------------+------------+---------------------+

Note how MySQL handles month ends: 31 January + 1 month is 28 February (2026 isn't a leap year). Units include SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER and YEAR, plus combined units like INTERVAL '1:30' HOUR_MINUTE.

Never add plain numbers to dates: '2026-09-30' + 1 converts the string to the number 2026 and returns 2027!

Differences between dates#

SQL

SELECT id,
       DATEDIFF(shipped_on, ordered_at)                   AS days_to_ship,
       TIMESTAMPDIFF(HOUR, ordered_at, shipped_on)        AS hours_to_ship
FROM orders;

Output

+----+--------------+---------------+
| id | days_to_ship | hours_to_ship |
+----+--------------+---------------+
|  1 |            2 |            38 |
|  2 |            3 |            49 |
|  3 |         NULL |          NULL |
|  4 |            1 |             5 |
+----+--------------+---------------+

DATEDIFF(a, b) is a − b in whole days and ignores the time part.
TIMESTAMPDIFF(unit, start, end) is end − start in any unit. Note the opposite argument order.
Any arithmetic with NULL gives NULL, so order 3, which hasn't shipped, shows NULL.

Computing someone's age correctly (it handles birthdays that haven't happened yet this year):

SQL

SELECT TIMESTAMPDIFF(YEAR, '2000-10-15', '2026-09-30') AS age;

Output

+------+
| age  |
+------+
|   25 |
+------+

Common date queries#

Orders in the last 7 days, written so that an index on ordered_at can be used:

SQL

SELECT id, customer FROM orders
WHERE ordered_at >= NOW() - INTERVAL 7 DAY;

Orders in February 2026, using a half-open range:

SQL

SELECT id, customer, ordered_at FROM orders
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01';

Output

+----+----------+---------------------+
| id | customer | ordered_at          |
+----+----------+---------------------+
|  2 | Rohan    | 2026-02-28 22:10:00 |
+----+----------+---------------------+

This form is better than WHERE MONTH(ordered_at) = 2 AND YEAR(ordered_at) = 2026: it gives the same answer, but MySQL can use an index because the column isn't wrapped in a function.

Orders per month:

SQL

SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS month, COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

Output

+---------+--------+
| month   | orders |
+---------+--------+
| 2026-01 |      1 |
| 2026-02 |      1 |
| 2026-03 |      1 |
| 2026-09 |      1 |
+---------+--------+

Time zones#

The server has a global time zone and each connection has a session time zone:

SQL

SELECT @@global.time_zone, @@session.time_zone;
SET time_zone = '+05:30';          -- this session only
SELECT CONVERT_TZ('2026-09-30 12:00:00', '+00:00', '+05:30') AS ist;

Output

+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
+---------------------+
| ist                 |
+---------------------+
| 2026-09-30 17:30:00 |
+---------------------+

Named zones such as 'Asia/Kolkata' or 'Europe/London' work only after the time-zone tables are loaded (mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql on Linux). Until then, CONVERT_TZ with names returns NULL. Named zones are worth loading because they handle daylight-saving time, which fixed offsets don't.

A robust convention: store UTC, convert at the edges. Have the app connect with time_zone = '+00:00', store DATETIME values in UTC, and convert to the user's zone only for display.

Unix timestamps#

Many APIs use seconds since 1970-01-01 UTC:

SQL

SELECT UNIX_TIMESTAMP('2026-01-01 00:00:00') AS secs, FROM_UNIXTIME(1767225600) AS back;

Output

+------------+---------------------+
| secs       | back                |
+------------+---------------------+
| 1767205800 | 2026-01-01 05:30:00 |
+------------+---------------------+

Both functions use the session time zone, which is +05:30 here after the SET above. That's why midnight local time gives 1767205800, and 1767225600 (midnight UTC) reads back as 05:30.

Common mistakes#

Using BETWEEN '2026-02-01' AND '2026-02-28' on a DATETIME and missing the 28th after midnight.
Swapping the argument order of DATEDIFF (a − b) and TIMESTAMPDIFF (end − start).
Storing dates as VARCHAR like '30/09/2026'. They won't sort or compare correctly, so convert them with STR_TO_DATE on import.
Mixing time zones: the database, the app server and users' browsers may all be in different zones. Decide on UTC storage early.

What's next#

Now you can transform single values. Next you'll summarise many rows at once with aggregate functions, GROUP BY and HAVING.

Sample data#

The current date and time#

Extracting parts of a date#

Formatting dates with DATE_FORMAT#

Date arithmetic#

Differences between dates#

Common date queries#

Time zones#

Unix timestamps#

Common mistakes#

What's next#

Quick quiz