Chapter 26 of 35

Date & Time Functions

Almost every real table has a date in it: when an employee joined, when an order was placed, when a parcel was shipped. And almost every business question is really a date question: "orders in the last 30 days", "revenue per month", "employees with more than 5 years of service", "delivery date = order date + 7 days".

In the previous chapter we learned functions that work on text. In this chapter we will learn the date and time functions of MySQL: how to get the current date, pull out parts of a date (year, month, weekday), format dates for display, add and subtract intervals, find the difference between two dates, and filter date ranges correctly.

All examples use the shared company_db sample database, with real MySQL 8.4 output. The employees.hire_date, customers.signup_date and orders.order_date columns are all of type DATE.


A Quick Recap of the Date Types

We met these types in the SQL Data Types chapter. Date functions behave slightly differently depending on the type, so here is a reminder:

Type

Stores

Example

DATE

Only a calendar date

2024-03-28

TIME

Only a time of day (or a duration)

14:45:10

DATETIME

Date and time, stored exactly as given

2024-03-28 14:45:10

TIMESTAMP

Date and time, stored internally in UTC and converted to the session time zone when read (range 1970 to 2038)

2024-03-28 14:45:10

YEAR

Only a year

2024

MySQL always writes date literals as 'YYYY-MM-DD' (year first). '15-01-2024' or '01/15/2024' are not dates to MySQL. When you receive dates in another format, convert them with STR_TO_DATE(), covered below.


Getting the Current Date and Time

SELECT NOW(), CURDATE(), CURTIME();
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2026-11-02 08:13:56 | 2026-11-02 | 08:13:56  |
+---------------------+------------+-----------+
1 row in set (0.00 sec)

Your output will of course show the date and time when you run it.

Function

Returns

Synonyms

NOW()

Current date and time (DATETIME)

CURRENT_TIMESTAMP, LOCALTIME

CURDATE()

Current date only

CURRENT_DATE

CURTIME()

Current time only

CURRENT_TIME

UTC_TIMESTAMP()

Current date and time in UTC, whatever the session time zone

-

SYSDATE()

The time at the exact moment the function runs

-

NOW() vs SYSDATE()

This is a favourite interview trick. NOW() returns the time at which the statement started, and it returns that same value everywhere inside the statement. SYSDATE() returns the time at which the function itself is evaluated. SLEEP(2) pauses for two seconds, which makes the difference visible:

SELECT NOW(), SLEEP(2), NOW(), SYSDATE();
+---------------------+----------+---------------------+---------------------+
| NOW()               | SLEEP(2) | NOW()               | SYSDATE()           |
+---------------------+----------+---------------------+---------------------+
| 2026-11-02 08:13:56 |        0 | 2026-11-02 08:13:56 | 2026-11-02 08:13:58 |
+---------------------+----------+---------------------+---------------------+
1 row in set (2.00 sec)

Both NOW() calls show :56 even though two seconds passed between them; SYSDATE() shows :58. Why does NOW() behave like this? Consistency. If you update 1 million rows with SET updated_at = NOW(), every row gets the same timestamp, which is what you want. Prefer NOW() in normal code.


Extracting Parts of a Date

Often you need just one piece of a date: the year someone joined, or the month of an order.

SELECT name, hire_date,
       YEAR(hire_date)  AS yr,
       MONTH(hire_date) AS mon,
       DAY(hire_date)   AS d
FROM employees
ORDER BY hire_date
LIMIT 4;
+--------------+------------+------+------+------+
| name         | hire_date  | yr   | mon  | d    |
+--------------+------------+------+------+------+
| Sneha Kapoor | 2017-11-20 | 2017 |   11 |   20 |
| Aarav Sharma | 2018-04-12 | 2018 |    4 |   12 |
| Priya Iyer   | 2019-07-01 | 2019 |    7 |    1 |
| Karan Patel  | 2019-09-23 | 2019 |    9 |   23 |
+--------------+------------+------+------+------+
4 rows in set (0.00 sec)

There are also functions that return names, weekdays and quarters:

SELECT order_id, order_date,
       MONTHNAME(order_date) AS month_name,
       DAYNAME(order_date)   AS day_name,
       DAYOFWEEK(order_date) AS dow,
       QUARTER(order_date)   AS q
FROM orders;
+----------+------------+------------+-----------+------+------+
| order_id | order_date | month_name | day_name  | dow  | q    |
+----------+------------+------------+-----------+------+------+
|     1001 | 2024-01-15 | January    | Monday    |    2 |    1 |
|     1002 | 2024-01-20 | January    | Saturday  |    7 |    1 |
|     1003 | 2024-02-02 | February   | Friday    |    6 |    1 |
|     1004 | 2024-02-14 | February   | Wednesday |    4 |    1 |
|     1005 | 2024-03-01 | March      | Friday    |    6 |    1 |
|     1006 | 2024-03-11 | March      | Monday    |    2 |    1 |
|     1007 | 2024-03-28 | March      | Thursday  |    5 |    1 |
|     1008 | 2024-04-02 | April      | Tuesday   |    3 |    2 |
+----------+------------+------------+-----------+------+------+
8 rows in set (0.00 sec)

Careful with weekday numbering, because MySQL has two functions that count differently:

Function

Sunday

Monday

Saturday

DAYOFWEEK()

1

2

7

WEEKDAY()

6

0

5

So a weekend check can be written as DAYOFWEEK(d) IN (1, 7) or WEEKDAY(d) >= 5.

EXTRACT(), DATE() and TIME()

EXTRACT(unit FROM value) is the standard SQL way to pull out a part. DATE() and TIME() split a DATETIME into its two halves.

SELECT EXTRACT(YEAR FROM '2024-03-28')          AS y,
       EXTRACT(MONTH FROM '2024-03-28')         AS m,
       EXTRACT(YEAR_MONTH FROM '2024-03-28')    AS ym,
       EXTRACT(HOUR FROM '2024-03-28 14:45:10') AS h;
+------+------+--------+------+
| y    | m    | ym     | h    |
+------+------+--------+------+
| 2024 |    3 | 202403 |   14 |
+------+------+--------+------+
1 row in set (0.00 sec)
SELECT DATE('2024-03-28 14:45:10') AS d,
       TIME('2024-03-28 14:45:10') AS t,
       DAYOFYEAR('2024-03-28')     AS doy;
+------------+----------+------+
| d          | t        | doy  |
+------------+----------+------+
| 2024-03-28 | 14:45:10 |   88 |
+------------+----------+------+
1 row in set (0.00 sec)

March 28 is day 88 of 2024: 31 (Jan) + 29 (Feb, leap year) + 28 = 88.


Formatting Dates: DATE_FORMAT()

MySQL shows dates as 2024-01-15, but an invoice for an Indian customer usually wants 15-01-2024 or 15 Jan 2024. DATE_FORMAT(date, format) turns a date into a string using format specifiers that start with %.

SELECT order_id,
       DATE_FORMAT(order_date, '%d-%m-%Y')     AS indian,
       DATE_FORMAT(order_date, '%d %b %Y')     AS short_txt,
       DATE_FORMAT(order_date, '%W, %D %M %Y') AS long_txt
FROM orders
WHERE order_id IN (1001, 1004, 1007);
+----------+------------+-------------+-------------------------------+
| order_id | indian     | short_txt   | long_txt                      |
+----------+------------+-------------+-------------------------------+
|     1001 | 15-01-2024 | 15 Jan 2024 | Monday, 15th January 2024     |
|     1004 | 14-02-2024 | 14 Feb 2024 | Wednesday, 14th February 2024 |
|     1007 | 28-03-2024 | 28 Mar 2024 | Thursday, 28th March 2024     |
+----------+------------+-------------+-------------------------------+
3 rows in set (0.00 sec)

And for times:

SELECT DATE_FORMAT('2024-03-28 14:05:09', '%H:%i:%s') AS t24,
       DATE_FORMAT('2024-03-28 14:05:09', '%h:%i %p') AS t12;
+----------+----------+
| t24      | t12      |
+----------+----------+
| 14:05:09 | 02:05 PM |
+----------+----------+
1 row in set (0.00 sec)

The most useful specifiers:

Specifier

Meaning

Example

%Y / %y

4-digit / 2-digit year

2024 / 24

%m / %c

Month 01-12 / 1-12

03 / 3

%M / %b

Full / short month name

March / Mar

%d / %e

Day 01-31 / 1-31

08 / 8

%D

Day with English suffix

28th

%W / %a

Full / short weekday name

Thursday / Thu

%H / %h

Hour 00-23 / 01-12

14 / 02

%i

Minutes 00-59

05

%s

Seconds 00-59

09

%p

AM or PM

PM

Minutes are %i, not %m. %m is the month. Writing '%H:%m' is one of the most common date bugs, and it gives no error, just wrong output.

One more important point: DATE_FORMAT() returns a string. Format dates at the very end, for display. Do the sorting, comparing and arithmetic on the real date column. For example, sorting by '%d-%m-%Y' text would put 01-02-2024 before 15-01-2024.


Parsing Text into Dates: STR_TO_DATE()

STR_TO_DATE(text, format) is the reverse of DATE_FORMAT(). You describe the format of the incoming text, and MySQL gives back a real date. This is what you use when importing a CSV file from Excel with dates like 15/01/2024.

SELECT STR_TO_DATE('15/01/2024', '%d/%m/%Y')                 AS d1,
       STR_TO_DATE('28-Mar-2024', '%d-%b-%Y')                AS d2,
       STR_TO_DATE('2024-03-28 2:05 PM', '%Y-%m-%d %h:%i %p') AS d3;
+------------+------------+---------------------+
| d1         | d2         | d3                  |
+------------+------------+---------------------+
| 2024-01-15 | 2024-03-28 | 2024-03-28 14:05:00 |
+------------+------------+---------------------+
1 row in set (0.00 sec)

If the text is not a valid date, you get NULL and a warning instead of an error:

SELECT STR_TO_DATE('31/02/2024', '%d/%m/%Y') AS bad;
SHOW WARNINGS;
+------+
| bad  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '31/02/2024' for function str_to_date |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

There is no 31st of February, so the result is NULL. When cleaning imported data, check for NULLs after conversion to catch bad rows.


Date Arithmetic: DATE_ADD(), DATE_SUB() and INTERVAL

To add time to a date, use DATE_ADD(date, INTERVAL n unit), or the shorter form date + INTERVAL n unit. Both do exactly the same thing. Suppose orders are delivered within 7 days and can be returned within 30 days:

SELECT order_id, order_date,
       DATE_ADD(order_date, INTERVAL 7 DAY) AS expected_delivery,
       order_date + INTERVAL 30 DAY        AS return_deadline
FROM orders
WHERE status IN ('Shipped', 'Pending');
+----------+------------+-------------------+-----------------+
| order_id | order_date | expected_delivery | return_deadline |
+----------+------------+-------------------+-----------------+
|     1005 | 2024-03-01 | 2024-03-08        | 2024-03-31      |
|     1007 | 2024-03-28 | 2024-04-04        | 2024-04-27      |
|     1008 | 2024-04-02 | 2024-04-09        | 2024-05-02      |
+----------+------------+-------------------+-----------------+
3 rows in set (0.00 sec)

Notice how MySQL handles month boundaries for you: 28 March + 7 days = 4 April. Subtracting works the same way, and you can use many units: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, plus combined units like YEAR_MONTH.

SELECT DATE_SUB('2024-03-01', INTERVAL 1 DAY)               AS day_before,
       '2024-03-01' - INTERVAL 1 MONTH                     AS month_before,
       DATE_ADD('2024-03-28 14:45:10', INTERVAL 90 MINUTE) AS plus90min,
       DATE_ADD('2024-03-28', INTERVAL '1-6' YEAR_MONTH)   AS plus_1y6m;
+------------+--------------+---------------------+------------+
| day_before | month_before | plus90min           | plus_1y6m  |
+------------+--------------+---------------------+------------+
| 2024-02-29 | 2024-02-01   | 2024-03-28 16:15:10 | 2025-09-28 |
+------------+--------------+---------------------+------------+
1 row in set (0.00 sec)

The day before 1 March 2024 is 29 February, because 2024 is a leap year. MySQL knows the calendar, so you never have to.

Adding Months to the End of a Month

What is 31 January + 1 month? There is no 31 February, so MySQL clamps the result to the last valid day of the target month:

SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH) AS jan31_plus1,
       DATE_ADD('2023-01-31', INTERVAL 1 MONTH) AS jan31_2023,
       DATE_ADD('2024-02-29', INTERVAL 1 YEAR)  AS leap_plus1y,
       LAST_DAY('2024-02-10')                   AS last_feb24,
       LAST_DAY('2023-02-10')                   AS last_feb23;
+-------------+------------+-------------+------------+------------+
| jan31_plus1 | jan31_2023 | leap_plus1y | last_feb24 | last_feb23 |
+-------------+------------+-------------+------------+------------+
| 2024-02-29  | 2023-02-28 | 2025-02-28  | 2024-02-29 | 2023-02-28 |
+-------------+------------+-------------+------------+------------+
1 row in set (0.00 sec)

LAST_DAY(d) returns the last day of the month containing d. It is handy for "month-end" reports and for billing cycles.

Do Not Use + 1 on a Date

Coming from other languages, you may try to add a day with + 1. Look what happens:

SELECT '2024-03-01' + 1 AS wrong;
SHOW WARNINGS;
+-------+
| wrong |
+-------+
|  2025 |
+-------+
1 row in set, 1 warning (0.00 sec)

+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2024-03-01' |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

The + operator is a number operator. MySQL converts the string to a number, reading digits until it hits the first -, gets 2024, and adds 1. Always write + INTERVAL 1 DAY.


Difference Between Two Dates

DATEDIFF(): Days Between Two Dates

DATEDIFF(end, start) returns end - start in days. The order of the arguments matters, and any time part is ignored:

SELECT DATEDIFF('2024-03-01', '2024-02-14')                   AS d1,
       DATEDIFF('2024-02-14', '2024-03-01')                   AS d2,
       DATEDIFF('2024-03-01 00:00:01', '2024-02-29 23:59:59') AS d3;
+------+------+------+
| d1   | d2   | d3   |
+------+------+------+
|   16 |  -16 |    1 |
+------+------+------+
1 row in set (0.00 sec)

In d3 the two moments are only 2 seconds apart, but they are on different calendar days, so DATEDIFF says 1. It compares dates, not durations.

TIMESTAMPDIFF(): Difference in Any Unit

TIMESTAMPDIFF(unit, start, end) returns the number of complete units between two values. Note that the argument order is the opposite of DATEDIFF: start comes first.

Let us compute each employee's service as on 31 December 2024. (We use a fixed date instead of CURDATE() so that your output matches this page. In a real report you would write CURDATE().)

SELECT name, hire_date,
       DATEDIFF('2024-12-31', hire_date)               AS days_worked,
       TIMESTAMPDIFF(YEAR,  hire_date, '2024-12-31')   AS full_years,
       TIMESTAMPDIFF(MONTH, hire_date, '2024-12-31')   AS full_months
FROM employees
ORDER BY hire_date;
+--------------+------------+-------------+------------+-------------+
| name         | hire_date  | days_worked | full_years | full_months |
+--------------+------------+-------------+------------+-------------+
| Sneha Kapoor | 2017-11-20 |        2598 |          7 |          85 |
| Aarav Sharma | 2018-04-12 |        2455 |          6 |          80 |
| Priya Iyer   | 2019-07-01 |        2010 |          5 |          65 |
| Karan Patel  | 2019-09-23 |        1926 |          5 |          63 |
| Rohan Mehta  | 2020-01-15 |        1812 |          4 |          59 |
| Arjun Reddy  | 2020-10-05 |        1548 |          4 |          50 |
| Vikram Singh | 2021-03-08 |        1394 |          3 |          45 |
| Ananya Das   | 2022-06-30 |         915 |          2 |          30 |
| Meera Nair   | 2023-02-14 |         686 |          1 |          22 |
| Ishita Gupta | 2024-01-10 |         356 |          0 |          11 |
+--------------+------------+-------------+------------+-------------+
10 rows in set (0.00 sec)

"Complete" is the key word. Ishita joined on 10 Jan 2024, so on 31 Dec 2024 she has 11 complete months and 0 complete years. Here is the boundary in action:

SELECT TIMESTAMPDIFF(YEAR, '2020-03-15', '2024-03-14') AS a,
       TIMESTAMPDIFF(YEAR, '2020-03-15', '2024-03-15') AS b,
       TIMESTAMPDIFF(HOUR, '2024-03-28 09:00:00', '2024-03-29 17:30:00') AS hrs,
       TIMEDIFF('17:30:00', '09:15:00') AS td;
+------+------+------+----------+
| a    | b    | hrs  | td       |
+------+------+------+----------+
|    3 |    4 |   32 | 08:15:00 |
+------+------+------+----------+
1 row in set (0.00 sec)

One day before the 4th anniversary, the answer is still 3 years. This is exactly how age is calculated, which is why TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) is the correct way to compute a person's age. A common wrong approach is YEAR(CURDATE()) - YEAR(birth_date), which ignores whether the birthday has come yet this year. TIMEDIFF() returns the difference between two times (or two datetimes) as a TIME value.

Worked Example: Days From Signup to First Order

Date functions combine naturally with joins and GROUP BY. How long did each customer take to place their first order after signing up?

SELECT c.name, c.signup_date,
       MIN(o.order_date) AS first_order,
       DATEDIFF(MIN(o.order_date), c.signup_date) AS days_to_first_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, c.signup_date
ORDER BY days_to_first_order;
+--------------+-------------+-------------+---------------------+
| name         | signup_date | first_order | days_to_first_order |
+--------------+-------------+-------------+---------------------+
| Neha Agarwal | 2023-05-09  | 2024-03-01  |                 297 |
| Aditya Joshi | 2023-03-22  | 2024-02-14  |                 329 |
| Kavya Menon  | 2023-02-17  | 2024-01-20  |                 337 |
| Rahul Verma  | 2023-01-05  | 2024-01-15  |                 375 |
+--------------+-------------+-------------+---------------------+
4 rows in set (0.00 sec)

Step by step: the join pairs each customer with their orders, MIN(o.order_date) picks the earliest order per customer, and DATEDIFF subtracts the signup date. Siddharth (no orders) is dropped by the inner join, and the guest order 1008 has no customer to match.


Grouping by Month, Year or Weekday

"Revenue per month" is one of the most common report queries. Group by a value computed from the date:

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;
+---------+--------+---------+
| month   | orders | revenue |
+---------+--------+---------+
| 2024-01 |      2 | 7300.00 |
| 2024-02 |      1 | 7300.00 |
| 2024-03 |      3 | 9650.00 |
| 2024-04 |      1 |  800.00 |
+---------+--------+---------+
4 rows in set (0.00 sec)

Here sorting the '%Y-%m' string is safe, because year-first strings with zero-padded months sort in the same order as the dates. Why not just GROUP BY MONTH(order_date)? Because January 2024 and January 2025 would fall into the same group. Always group by year and month unless you really want to combine years.

Months with no orders simply do not appear. Filling those gaps with zero needs a generated list of months, which we will build with a recursive CTE in the Common Table Expressions (WITH) chapter.

Grouping by weekday, ordered Monday to Saturday:

SELECT DAYNAME(order_date) AS weekday, COUNT(*) AS orders
FROM orders
GROUP BY DAYNAME(order_date), WEEKDAY(order_date)
ORDER BY WEEKDAY(order_date);
+-----------+--------+
| weekday   | orders |
+-----------+--------+
| Monday    |      2 |
| Tuesday   |      1 |
| Wednesday |      1 |
| Thursday  |      1 |
| Friday    |      2 |
| Saturday  |      1 |
+-----------+--------+
6 rows in set (0.00 sec)

Ordering by DAYNAME alone would sort alphabetically (Friday, Monday, Saturday...). We order by the weekday number instead, and include it in GROUP BY so the query is valid under MySQL's ONLY_FULL_GROUP_BY rule.


Filtering Date Ranges Correctly

All orders placed in February 2024. The clean, recommended pattern is a half-open range: greater than or equal to the start, strictly less than the next period's start.

SELECT order_id, order_date, amount
FROM orders
WHERE order_date >= '2024-02-01'
  AND order_date <  '2024-03-01';
+----------+------------+---------+
| order_id | order_date | amount  |
+----------+------------+---------+
|     1003 | 2024-02-02 | 1200.00 |
|     1004 | 2024-02-14 | 7300.00 |
+----------+------------+---------+
2 rows in set (0.00 sec)

"Last 30 days" works the same way. With a real application you would write CURDATE() - INTERVAL 30 DAY; here we pretend today is 1 March 2024:

SELECT order_id, order_date
FROM orders
WHERE order_date >= '2024-03-01' - INTERVAL 30 DAY
  AND order_date <  '2024-03-01';
+----------+------------+
| order_id | order_date |
+----------+------------+
|     1003 | 2024-02-02 |
|     1004 | 2024-02-14 |
+----------+------------+
2 rows in set (0.00 sec)

You could also write WHERE YEAR(order_date) = 2024 AND MONTH(order_date) = 2. It gives the same rows, but it has a hidden cost: because the column is wrapped in a function, MySQL cannot use an index on order_date and must compute the function for every row. We will see exactly why in the Indexes chapter. Keep the column bare on one side of the comparison whenever you can.

The BETWEEN Trap With DATETIME

With a DATE column, BETWEEN is fine. With a DATETIME column it hides a nasty bug. Let us make a small practice table of shipments that records the exact moment each parcel left the warehouse (create it in your own copy of the database):

CREATE TABLE shipments (
  shipment_id INT PRIMARY KEY,
  order_id    INT,
  shipped_at  DATETIME NOT NULL
);
INSERT INTO shipments VALUES
 (1, 1001, '2024-01-16 10:30:00'),
 (2, 1002, '2024-01-21 18:05:00'),
 (3, 1004, '2024-02-14 09:00:00'),
 (4, 1006, '2024-03-11 23:45:00'),
 (5, 1005, '2024-03-02 00:00:00');

Now, "all shipments from 1 March to 11 March":

SELECT * FROM shipments
WHERE shipped_at BETWEEN '2024-03-01' AND '2024-03-11';
+-------------+----------+---------------------+
| shipment_id | order_id | shipped_at          |
+-------------+----------+---------------------+
|           5 |     1005 | 2024-03-02 00:00:00 |
+-------------+----------+---------------------+
1 row in set (0.00 sec)

Shipment 4 on 11 March is missing! The reason: when compared with a DATETIME, '2024-03-11' means '2024-03-11 00:00:00', midnight at the start of the day. Anything later on the 11th is outside the range. The half-open range fixes it:

SELECT * FROM shipments
WHERE shipped_at >= '2024-03-01' AND shipped_at < '2024-03-12';
+-------------+----------+---------------------+
| shipment_id | order_id | shipped_at          |
+-------------+----------+---------------------+
|           4 |     1006 | 2024-03-11 23:45:00 |
|           5 |     1005 | 2024-03-02 00:00:00 |
+-------------+----------+---------------------+
2 rows in set (0.00 sec)

This pattern is correct for DATE, DATETIME and TIMESTAMP columns alike, which is why experienced developers use it everywhere.

Mixing DATE and DATETIME

How many hours did each order take to ship? order_date is a DATE (treated as midnight) and shipped_at is a DATETIME:

SELECT s.shipment_id, s.shipped_at,
       DATE(s.shipped_at)                  AS ship_day,
       TIME_FORMAT(s.shipped_at, '%h:%i %p') AS ship_time,
       TIMESTAMPDIFF(HOUR, o.order_date, s.shipped_at) AS hours_to_ship
FROM shipments s
JOIN orders o ON o.order_id = s.order_id
ORDER BY s.shipment_id;
+-------------+---------------------+------------+-----------+---------------+
| shipment_id | shipped_at          | ship_day   | ship_time | hours_to_ship |
+-------------+---------------------+------------+-----------+---------------+
|           1 | 2024-01-16 10:30:00 | 2024-01-16 | 10:30 AM  |            34 |
|           2 | 2024-01-21 18:05:00 | 2024-01-21 | 06:05 PM  |            42 |
|           3 | 2024-02-14 09:00:00 | 2024-02-14 | 09:00 AM  |             9 |
|           4 | 2024-03-11 23:45:00 | 2024-03-11 | 11:45 PM  |            23 |
|           5 | 2024-03-02 00:00:00 | 2024-03-02 | 12:00 AM  |            24 |
+-------------+---------------------+------------+-----------+---------------+
5 rows in set (0.00 sec)

Order 1001 was placed on 15 Jan (taken as 00:00) and shipped at 10:30 the next day: 34 complete hours. TIME_FORMAT() is like DATE_FORMAT() but only for the time part.

Invalid Dates Are Rejected

MySQL 8 runs in strict mode by default, so impossible dates are refused when you store them:

INSERT INTO shipments VALUES (6, 1007, '2024-02-30 10:00:00');
ERROR 1292 (22007): Incorrect datetime value: '2024-02-30 10:00:00' for column 'shipped_at' at row 1

Time Zones: DATETIME vs TIMESTAMP

An app with users in India and servers in a UTC data centre must think about time zones. The two date-time types behave differently:

  • DATETIME stores exactly what you give it. It knows nothing about time zones.

  • TIMESTAMP converts the value from the session time zone to UTC when storing, and back to the session time zone when reading.

Let us store the same value in both, then change the session time zone to India (+05:30):

CREATE TABLE tz_demo (dt DATETIME, ts TIMESTAMP);
SET time_zone = '+00:00';
INSERT INTO tz_demo VALUES ('2024-03-28 09:00:00', '2024-03-28 09:00:00');
SELECT * FROM tz_demo;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2024-03-28 09:00:00 | 2024-03-28 09:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
SET time_zone = '+05:30';
SELECT * FROM tz_demo;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2024-03-28 09:00:00 | 2024-03-28 14:30:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

The TIMESTAMP column now shows 14:30, the same instant expressed in Indian time. The DATETIME is unchanged. To convert a value yourself, use CONVERT_TZ():

SELECT CONVERT_TZ('2024-03-28 09:00:00', '+00:00', '+05:30') AS ist;
+---------------------+
| ist                 |
+---------------------+
| 2024-03-28 14:30:00 |
+---------------------+
1 row in set (0.00 sec)

Numeric offsets like '+05:30' always work. Named zones like 'Asia/Kolkata' only work if the server's time zone tables have been loaded (with the mysql_tzinfo_to_sql tool); otherwise CONVERT_TZ() quietly returns NULL. A common practical rule: store moments in UTC, and convert to the user's zone when displaying.

Unix Timestamps

Many APIs and log files use Unix time: the number of seconds since 1970-01-01 00:00:00 UTC. With the session time zone set to UTC:

SET time_zone = '+00:00';
SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00') AS epoch,
       FROM_UNIXTIME(1704067200)             AS back;
+------------+---------------------+
| epoch      | back                |
+------------+---------------------+
| 1704067200 | 2024-01-01 00:00:00 |
+------------+---------------------+
1 row in set (0.00 sec)

Both functions interpret the date-time in the session time zone, so with time_zone = '+05:30' you would get different numbers for the same text. Keep that in mind when comparing results across servers.


Handy Recipes

SELECT order_date,
       DATE_FORMAT(order_date, '%Y-%m-01')                 AS month_start,
       LAST_DAY(order_date)                                AS month_end,
       DATE_SUB(order_date, INTERVAL WEEKDAY(order_date) DAY) AS week_monday
FROM orders
WHERE order_id IN (1001, 1007);
+------------+-------------+------------+-------------+
| order_date | month_start | month_end  | week_monday |
+------------+-------------+------------+-------------+
| 2024-01-15 | 2024-01-01  | 2024-01-31 | 2024-01-15  |
| 2024-03-28 | 2024-03-01  | 2024-03-31 | 2024-03-25  |
+------------+-------------+------------+-------------+
2 rows in set (0.00 sec)

Task

Expression

First day of the month

DATE_FORMAT(d, '%Y-%m-01') or d - INTERVAL DAY(d) - 1 DAY

Last day of the month

LAST_DAY(d)

Monday of the week

d - INTERVAL WEEKDAY(d) DAY

Age in years

TIMESTAMPDIFF(YEAR, birth_date, CURDATE())

Is it a weekend?

WEEKDAY(d) >= 5

Today's rows (DATETIME column)

col >= CURDATE() AND col < CURDATE() + INTERVAL 1 DAY


Common Mistakes

Mistake

What happens

Fix

Writing dates as '15-01-2024'

Not recognised as a date

Use 'YYYY-MM-DD', or STR_TO_DATE() to convert

date + 1

Numeric addition (2024 + 1 = 2025)

date + INTERVAL 1 DAY

%m for minutes in DATE_FORMAT

Month shown instead of minutes

Minutes are %i

BETWEEN '...-01' AND '...-11' on DATETIME

Misses rows after midnight on the last day

>= start AND < next_day

Grouping by MONTH(d) only

Same month of different years merged

Group by year and month

Sorting by a formatted string like '%d-%m-%Y'

Text order, not date order

Sort by the date column itself

Age as YEAR(now) - YEAR(birth)

Off by one before the birthday

TIMESTAMPDIFF(YEAR, ...)

Swapping DATEDIFF / TIMESTAMPDIFF argument order

Negative results

DATEDIFF(end, start); TIMESTAMPDIFF(unit, start, end)

Wrapping an indexed date column in a function in WHERE

Index cannot be used, slow on big tables

Rewrite as a range on the bare column


Interview Questions

What is the difference between NOW() and SYSDATE()?

NOW() returns the time the statement started and is constant within the statement. SYSDATE() returns the time at the moment it is evaluated, so it can differ within one statement.

What is the difference between DATETIME and TIMESTAMP?

DATETIME stores the value as given, with no time zone handling, and has a range of years 1000 to 9999. TIMESTAMP is converted to UTC for storage and back to the session time zone when read; its range is 1970 to 2038.

How do you find employees who joined in the last 6 months?

SELECT * FROM employees WHERE hire_date >= CURDATE() - INTERVAL 6 MONTH;

DATEDIFF vs TIMESTAMPDIFF?

DATEDIFF(a, b) returns a - b in days only and ignores time parts. TIMESTAMPDIFF(unit, start, end) returns the number of complete units (seconds to years) between start and end.

How do you calculate a person's age in SQL?

TIMESTAMPDIFF(YEAR, birth_date, CURDATE()). It counts complete years, so it handles birthdays that have not yet arrived this year.

Why should you avoid WHERE YEAR(order_date) = 2024?

Applying a function to the column prevents MySQL from using an index on it. WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' returns the same rows and can use the index.

What does DATE_ADD('2024-01-31', INTERVAL 1 MONTH) return?

2024-02-29. When the day does not exist in the target month, MySQL uses the last day of that month.

How do you convert '15/01/2024' to a DATE?

STR_TO_DATE('15/01/2024', '%d/%m/%Y'). If the text is invalid, it returns NULL with a warning.

How do you get monthly totals from an orders table?

Group by year and month, for example GROUP BY DATE_FORMAT(order_date, '%Y-%m') or GROUP BY YEAR(order_date), MONTH(order_date), with SUM(amount).


Summary

  • NOW(), CURDATE(), CURTIME() give the current moment; NOW() is fixed for the whole statement.

  • YEAR(), MONTH(), DAY(), DAYNAME(), QUARTER(), EXTRACT() pull out parts of a date. Watch the two weekday numberings.

  • DATE_FORMAT() turns a date into display text (%i = minutes); STR_TO_DATE() turns text into a date.

  • Add and subtract with DATE_ADD/DATE_SUB or + INTERVAL n unit; never with + 1. Month arithmetic clamps to the month's last day.

  • DATEDIFF(end, start) counts days; TIMESTAMPDIFF(unit, start, end) counts complete units.

  • Filter ranges with >= start AND < next_start, which is correct for DATETIME and keeps indexes usable.

  • TIMESTAMP is time-zone aware; DATETIME is not.

Our queries are getting longer: joins, groups and date logic together. In the next chapter, Views, we will learn how to save such a query under a name and use it like a table.