Date & Time Functions

Almost every real table has a date in it: when an order was placed, when a student joined, when a train leaves. Reports ask questions such as "sales this month", "customers who have not ordered for 90 days" or "staff who complete five years in June". Answering them means pulling dates apart, doing arithmetic on them and printing them in a friendly format.

You met a few date functions in String, Number & Date Functions. This lesson goes further: taking dates apart, parsing Indian-style dates, adding months safely, measuring months and years, time zones, and choosing the right column type. Most of these functions exist only in MySQL, so those examples show MySQL's output. The last section shows date techniques that also run in the browser.

The current date and time

MySQL has several functions that read the clock:

SELECT NOW(),             -- 2025-06-10 14:32:05  date and time
       CURDATE(),         -- 2025-06-10           date only
       CURTIME(),         -- 14:32:05             time only
       UTC_TIMESTAMP(),   -- 2025-06-10 09:02:05  date and time in UTC
       NOW(3);            -- 2025-06-10 14:32:05.418  with milliseconds

The comments show what you might see; your values depend on when you run the query. CURRENT_TIMESTAMP and CURRENT_DATE are the standard SQL names for NOW() and CURDATE() and work in most databases.

NOW() returns the moment the statement started, so it gives the same value in every row, even if the query runs for a minute. SYSDATE() returns the moment it is called, which can differ between rows. Prefer NOW().

Taking a date apart

EXTRACT(unit FROM date) is the standard way to pull out one part of a date. MySQL also has a short function for each part:

SELECT order_id, order_date,
       EXTRACT(YEAR FROM order_date) AS yr,
       QUARTER(order_date) AS qtr,
       MONTHNAME(order_date) AS month_name,
       DAYOFWEEK(order_date) AS day_of_week,
       WEEKDAY(order_date) AS weekday_no,
       LAST_DAY(order_date) AS month_end
FROM orders
WHERE order_id IN (101, 106, 110, 115);

order_id | order_date | yr   | qtr | month_name | day_of_week | weekday_no | month_end
---------+------------+------+-----+------------+-------------+------------+-----------
101      | 2025-01-05 | 2025 | 1   | January    | 1           | 6          | 2025-01-31
106      | 2025-02-28 | 2025 | 1   | February   | 6           | 4          | 2025-02-28
110      | 2025-04-01 | 2025 | 2   | April      | 3           | 1          | 2025-04-30
115      | 2025-05-20 | 2025 | 2   | May        | 3           | 1          | 2025-05-31
(4 rows)

Watch the two weekday functions, because they count differently. DAYOFWEEK() gives 1 for Sunday up to 7 for Saturday. WEEKDAY() gives 0 for Monday up to 6 for Sunday. 5 January 2025 was a Sunday, so it gets 1 and 6. LAST_DAY() returns the last date of the month, which is handy for month-end reports and knows that February 2025 has 28 days.

Formatting and parsing dates

DATE_FORMAT() turns a date into text. Indian invoices often use day-month-year, and a few extra codes give longer labels:

SELECT order_id,
       DATE_FORMAT(order_date, '%a, %D %M %Y') AS long_date,
       DATE_FORMAT(order_date, '%d/%m/%y') AS short_date
FROM orders
WHERE order_id IN (101, 113);

order_id | long_date             | short_date
---------+-----------------------+-----------
101      | Sun, 5th January 2025 | 05/01/25
113      | Fri, 2nd May 2025     | 02/05/25
(2 rows)

STR_TO_DATE() does the opposite: it reads text in a format you describe and returns a real date. You need it when a form or a CSV file gives you dates like 05/01/2025:

SELECT STR_TO_DATE('05/01/2025', '%d/%m/%Y') AS from_form,
       STR_TO_DATE('15-Aug-2025 9:30 PM', '%d-%b-%Y %h:%i %p') AS from_ticket;

from_form  | from_ticket
-----------+--------------------
2025-01-05 | 2025-08-15 21:30:00
(1 row)

Without the format string, MySQL cannot know whether 05/01/2025 means 5 January or 1 May. Always convert such input to YYYY-MM-DD before storing it.

Date arithmetic

DATE_ADD() and DATE_SUB() move a date by an interval: a number plus a unit such as DAY, WEEK, MONTH, YEAR, HOUR or MINUTE. You can also write + INTERVAL and - INTERVAL directly:

SELECT '2025-01-31' + INTERVAL 1 MONTH AS jan31_plus_month,
       DATE_ADD('2024-01-31', INTERVAL 1 MONTH) AS leap_year,
       DATE_SUB('2025-03-01', INTERVAL 1 DAY) AS day_before,
       '2025-05-20' - INTERVAL 2 WEEK AS two_weeks_earlier,
       DATE_ADD('2025-05-20 18:30:00', INTERVAL 90 MINUTE) AS ninety_minutes_later;

jan31_plus_month | leap_year  | day_before | two_weeks_earlier | ninety_minutes_later
-----------------+------------+------------+-------------------+---------------------
2025-02-28       | 2024-02-29 | 2025-02-28 | 2025-05-06        | 2025-05-20 20:00:00
(1 row)

Adding a month to 31 January gives the last valid day of February, not 3 March. That is usually what a billing system wants, but remember it when you add a month and then subtract it again.

To measure the gap between two dates, DATEDIFF(later, earlier) counts days. For months or years, use TIMESTAMPDIFF(unit, start, end). Note the opposite argument order. It counts only complete units, which is exactly what "years of service" means:

SELECT first_name, hire_date,
       DATEDIFF('2025-06-30', hire_date) AS days,
       TIMESTAMPDIFF(MONTH, hire_date, '2025-06-30') AS months,
       TIMESTAMPDIFF(YEAR, hire_date, '2025-06-30') AS full_years
FROM employees
WHERE employee_id IN (1, 4, 8, 10);

first_name | hire_date  | days | months | full_years
-----------+------------+------+--------+-----------
Rajesh     | 2018-02-01 | 2706 | 88     | 7
Pooja      | 2022-01-10 | 1267 | 41     | 3
Nisha      | 2024-03-04 | 483  | 15     | 1
Tanvi      | 2025-02-17 | 133  | 4      | 0
(4 rows)

A fixed date is used here so the output stays the same; in a real report you would write CURDATE(). Dividing DATEDIFF() by 365 to get years is a common mistake, because leap years make the answer drift by a day.

Storing dates and time zones

Pick the column type by what the value means. The details are in MySQL Data Types:

• DATE for a calendar day with no time: date of birth, order date.
• DATETIME for a moment written as local clock time: an exam that starts at 10:00 in Jaipur. Add precision with DATETIME(3) or DATETIME(6) for milliseconds or microseconds.
• TIMESTAMP for "when did this happen": MySQL converts it from the session time zone to UTC when saving, and back when reading. Its range ends in January 2038.

Every connection has a time zone setting. SELECT @@global.time_zone, @@session.time_zone; shows it, and SYSTEM means "whatever the server's operating system uses". You can change it for your connection. A UTC offset such as '+05:30' always works. Names such as 'Asia/Kolkata' work only after the server's time zone tables have been loaded. This experiment shows the difference between the two types:

CREATE TABLE clock_test (saved_dt DATETIME, saved_ts TIMESTAMP NULL);

SET time_zone = '+05:30';   -- India
INSERT INTO clock_test VALUES ('2025-01-05 10:00:00', '2025-01-05 10:00:00');

SET time_zone = '+00:00';   -- UTC, as a server in London would use
SELECT saved_dt, saved_ts FROM clock_test;

saved_dt            | saved_ts
--------------------+--------------------
2025-01-05 10:00:00 | 2025-01-05 04:30:00
(1 row)

The DATETIME kept the exact clock time. The TIMESTAMP was stored as UTC and is now shown in the new session zone. CONVERT_TZ('2025-01-05 10:00:00', '+00:00', '+05:30') converts a value yourself and returns 2025-01-05 15:30:00. A good rule for apps with users in several countries: store moments in UTC, and convert to the user's zone only when you display them. India has no daylight saving time, but many countries do, which is another reason to keep stored values in UTC.

Comparing and grouping dates (works everywhere)

Dates written as YYYY-MM-DD have a useful property: sorting them as text gives the same order as sorting them as dates. The browser's SQLite has no real date type and stores them as text, while MySQL converts the text to a date. Either way, comparisons, MIN, MAX and ORDER BY give correct answers. That makes these queries portable:

SELECT SUBSTR(order_date, 1, 7) AS month,
       COUNT(*) AS orders,
       MIN(order_date) AS first_order,
       MAX(order_date) AS last_order
FROM orders
GROUP BY SUBSTR(order_date, 1, 7)
ORDER BY month;

month   | orders | first_order | last_order
--------+--------+-------------+-----------
2025-01 | 3      | 2025-01-05  | 2025-01-20
2025-02 | 3      | 2025-02-02  | 2025-02-28
2025-03 | 3      | 2025-03-09  | 2025-03-22
2025-04 | 3      | 2025-04-01  | 2025-04-18
2025-05 | 3      | 2025-05-02  | 2025-05-20
(5 rows)

For a range, the safest pattern is a half-open range: "on or after the first day, and before the first day of the next period". It matters as soon as the column has a time part. Here a small table of website logins shows the trap with BETWEEN:

CREATE TABLE logins (
  login_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  login_at DATETIME NOT NULL
);

INSERT INTO logins (login_id, customer_id, login_at) VALUES
  (1, 1, '2025-03-01 09:15:00'),
  (2, 3, '2025-03-15 22:40:00'),
  (3, 5, '2025-03-31 18:45:00'),
  (4, 2, '2025-04-01 00:05:00');

SELECT SUM(CASE WHEN login_at BETWEEN '2025-03-01' AND '2025-03-31' THEN 1 ELSE 0 END) AS using_between,
       SUM(CASE WHEN login_at >= '2025-03-01' AND login_at < '2025-04-01' THEN 1 ELSE 0 END) AS half_open_range
FROM logins;

using_between | half_open_range
--------------+----------------
2             | 3
(1 row)

March had three logins, but BETWEEN found only two. '2025-03-31' means midnight at the start of 31 March, so the login at 18:45 that evening falls outside. The half-open range catches every moment of the month, and it can use an index on the column, unlike MONTH(login_at) = 3.

Common mistakes

• Storing '05-01-2025' as text. It sorts wrongly and is ambiguous. Use DATE and convert input with STR_TO_DATE().
• BETWEEN on a DATETIME column. The end date stops at midnight. Use >= start and < the next day.
• Swapping the arguments. DATEDIFF(later, earlier) but TIMESTAMPDIFF(unit, earlier, later). A negative answer means they are the wrong way round.
• Mixing time zones. If PHP works in IST and MySQL in UTC, NOW() and PHP's date() disagree by 5 hours 30 minutes. Set the zone explicitly in both.

Key points

• NOW(), CURDATE() and UTC_TIMESTAMP() read the clock; NOW() is fixed for the whole statement.
• EXTRACT(), QUARTER(), MONTHNAME(), DAYOFWEEK(), WEEKDAY() and LAST_DAY() take dates apart.
• DATE_FORMAT() turns dates into text; STR_TO_DATE() turns text into dates.
• DATE_ADD() and INTERVAL move dates; adding a month clamps to the last valid day.
• DATEDIFF() counts days; TIMESTAMPDIFF() counts complete months or years.
• TIMESTAMP is stored in UTC and shown in the session time zone; DATETIME is stored as given.
• YYYY-MM-DD values compare and sort correctly as text; use half-open ranges for periods.

SELECT first_name, hire_date,
       TIMESTAMPDIFF(YEAR, hire_date, '2025-06-30') AS years_of_service,
       hire_date + INTERVAL (TIMESTAMPDIFF(YEAR, hire_date, '2025-06-30') + 1) YEAR AS next_anniversary
FROM employees
ORDER BY hire_date;

SELECT SUBSTR(hire_date, 1, 4) AS hire_year, COUNT(*) AS hired
FROM employees
GROUP BY SUBSTR(hire_date, 1, 4)
ORDER BY hire_year;