Extra topic · Clock-watching

SQL Date Functions: NOW, DATE_ADD, DATEDIFF Explained

Working with dates and times: DATE and DATETIME, today and now, date ranges, adding and subtracting days, and the gap between two dates.

Covers: DATE · DATETIME · NOW · CURDATE · date ranges · DATE_ADD · DATE_SUB · DATEDIFF · TIMESTAMPDIFF · YEAR / MONTH

Practise Dates & Times

The lesson

Every example runs against the same small pizza shop database: pizzas, customers, orders and staff.

# Dates and times Most tables record when things happen: when an order was placed, when it was delivered. This level is about asking questions of those dates, like the orders from last week or how long a delivery took. Dates are written 'YYYY-MM-DD HH:MM:SS': year, month, day, then the time. The biggest unit comes first, so they sort correctly. - `DATE` a day: '2026-10-23' - `DATETIME` a day and a time: '2026-10-23 19:00:00' > Always write dates as 'YYYY-MM-DD'. '10/09/2026' means different days in the UK and the US.

SELECT id,
  status, ordered_at, delivered_at
FROM orders

# Now and today - `NOW()` this moment, date and time - `CURDATE()` today, with no time In this course the clock is fixed at Friday 23 October 2026, 7pm, so everyone gets the same answers. In real MySQL, NOW() keeps moving. > NOW() uses the database server's time zone, which may not be yours. Check it before comparing with times from your app.

SELECT NOW(), CURDATE()

# Filter by date - `WHERE ordered_at >= '2026-10-20'` on or after 20 October Dates go in quotes, like text. > '2026-10-20' means midnight at the start of that day, so >= includes all of the 20th.

SELECT id, ordered_at FROM orders
WHERE ordered_at >= '2026-10-20'

# The BETWEEN trap '2026-09-30' means midnight at the start of 30 September. BETWEEN '2026-09-01' AND '2026-09-30' stops there, so order 9, at 8pm on the 30th, is missed. - `>= '2026-09-01'` from the first day - `< '2026-10-01'` up to, but not including, the day after the last > For whole days, always use >= the first day and < the day after. It works whether or not the column stores a time.

SELECT id, ordered_at FROM orders
WHERE ordered_at >= '2026-09-01'
  AND ordered_at < '2026-10-01'

# Move a date: DATE_ADD and DATE_SUB - `DATE_SUB(NOW(), INTERVAL 7 DAY)` one week ago - `DATE_ADD(…, INTERVAL 1 MONTH)` one month later - `MINUTE` `HOUR` `DAY` `WEEK` `MONTH` `YEAR` the units you can move by > "The last 7 days" is ordered_at >= DATE_SUB(NOW(), INTERVAL 7 DAY).

SELECT id, ordered_at FROM orders
WHERE ordered_at >= DATE_SUB(NOW(),
  INTERVAL 7 DAY)

# Days between two dates: DATEDIFF - `DATEDIFF(later, earlier)` the number of days in between - `AS days_here` names the result column > The later date goes first. Swap them and you get a negative number.

SELECT name, hired_on,
  DATEDIFF(CURDATE(),
    hired_on) AS days_here
FROM staff

# Minutes and hours: TIMESTAMPDIFF - `TIMESTAMPDIFF(MINUTE, start, end)` the minutes from start to end - `IS NOT NULL` skips the orders that aren't delivered yet An undelivered order has no delivered_at, so its answer would be NULL. > Here the start comes first, the opposite of DATEDIFF. Check the sign of the answer.

SELECT id,
  TIMESTAMPDIFF(MINUTE, ordered_at,
    delivered_at) AS minutes
FROM orders
WHERE delivered_at IS NOT NULL

# Take a date apart - `YEAR()` `MONTH()` `DAY()` one part of the date - `DATE()` just the day, without the time This asks for September 2026 a second way. > Prefer the >= and < version. A function around a column, like MONTH(ordered_at), stops the database from using an index on it.

SELECT id, DATE(ordered_at) AS day
FROM orders
WHERE YEAR(ordered_at) = 2026
  AND MONTH(ordered_at) = 9

Appian corner

Appian’s Date type is a DATE column and Date and Time is a DATETIME. Appian stores Date and Time values in GMT and shows them in each user’s time zone; plain Date values are never converted. So an order placed at 11pm in New York is already tomorrow in the database: filter on a plain Date when you mean “that day”. In expressions, now() and today() are NOW() and CURDATE(), and a!queryFilter takes dates for ">=", "<" and "between".

Practice questions

Try each one before opening the answer. To write and run them against the database in your browser, practise the level.

1. Orders placed on or after 20 October 2026.

SELECT *
FROM orders
WHERE ordered_at ___ '2026-10-20'

Show the answer

SELECT *
FROM orders
WHERE ordered_at >= '2026-10-20'

On or after: greater than or equal to.

2. Every order placed in October 2026, using YEAR() and MONTH().
Show the answer
```
SELECT *
FROM orders
WHERE YEAR(ordered_at) = 2026
  AND MONTH(ordered_at) = 10
```
YEAR(ordered_at) = 2026 AND MONTH(ordered_at) = 10
3. Why does this miss order 9, placed at 8pm on 30 September?
```
WHERE ordered_at BETWEEN
  '2026-09-01' AND '2026-09-30'
```
- BETWEEN does not work on dates
- '2026-09-30' means midnight at the start of the 30th
- BETWEEN leaves out both ends
- Order 9 is cancelled
Show the answer
'2026-09-30' means midnight at the start of the 30th. A date with no time is midnight. Anything later that day is after the end of the range.
4. Every order placed in September 2026, including the one late on the 30th.
Show the answer
```
SELECT *
FROM orders
WHERE ordered_at >= '2026-09-01'
  AND ordered_at < '2026-10-01'
```
>= the first day AND < the first day of the next month: '2026-10-01'
5. Orders from the last 3 days: placed on or after exactly 3 days before NOW().
Show the answer
```
SELECT *
FROM orders
WHERE ordered_at >= DATE_SUB(NOW(),
  INTERVAL 3 DAY)
```
ordered_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
6. A follow-up email goes out 2 days after each delivered order was placed. Show each delivered order’s id and the follow-up time, in a column called follow_up.
Show the answer
```
SELECT id,
  DATE_ADD(ordered_at,
    INTERVAL 2 DAY) AS follow_up
FROM orders
WHERE status = 'delivered'
```
DATE_ADD(ordered_at, INTERVAL 2 DAY) AS follow_up, and WHERE status = 'delivered'
7. Each staff member’s name and how many days they’ve worked here, in a column called days_here.
Show the answer
```
SELECT name,
  DATEDIFF(CURDATE(),
    hired_on) AS days_here
FROM staff
```
DATEDIFF(CURDATE(), hired_on) AS days_here
8. Every delivered order’s id and how many minutes the delivery took.
Show the answer
```
SELECT id,
  TIMESTAMPDIFF(MINUTE, ordered_at,
    delivered_at)
FROM orders
WHERE delivered_at IS NOT NULL
```
TIMESTAMPDIFF(MINUTE, ordered_at, delivered_at), and leave out orders not delivered yet: delivered_at IS NOT NULL

9. Boss question! Orders placed in October 2026 that took more than 30 minutes to deliver: the id and the minutes.

Show the answer

SELECT id,
  TIMESTAMPDIFF(MINUTE, ordered_at,
    delivered_at)
FROM orders
WHERE ordered_at >= '2026-10-01'
  AND ordered_at < '2026-11-01'
  AND TIMESTAMPDIFF(MINUTE,
    ordered_at, delivered_at) > 30

Two filters joined with AND: the October range, and TIMESTAMPDIFF(MINUTE, ordered_at, delivered_at) > 30

Practise Dates & Times