Working with Dates

Part 1: Concept Notes: Working with Dates

Introduction

What is it? SQL has many built-in functions and rules for working with dates and times. You can take a date apart (year, month, day, hour), filter rows between two dates, find the earliest and latest event, and measure how much time passed between two moments.

Why do we need it? Almost every real table has a date or time column: when an order was placed, when a user logged in, when a ticket was opened. Nearly every business question is about time: "How many orders last month?", "When did each user first log in?", "How long did delivery take?" Dates also hide some of the most common SQL bugs, such as missing the last day of a range, or making a query slow by using a function on an indexed column.

Real-world analogy Think of a wall calendar with a stopwatch next to it. The calendar tells you which day something happened. The stopwatch tells you how long something took. SQL date functions let you read the calendar (extract parts, filter ranges) and use the stopwatch (differences).

Business use case A food-delivery company wants to know how many orders were placed each month, which deliveries took longer than 30 minutes, and which users logged in today. All three questions need date and time functions.

Dataset Used

employees (used for extracting date parts)

orders (used for date ranges)

logins (used for DATETIME ranges and first/latest events)

The login_id is the row number in the order listed. The "logged in today" query later in this chapter uses it.

support_tickets (used for date differences). The columns are ticket_id, customer, opened_on, and status. The full rows are not given in the source, so only the query and its output are shown.

deliveries (used for time differences). The columns are order_id, order_placed_at, and delivered_at. The rows appear in the output of the delivery examples.

Things to notice, because they matter later:

• Employee 5 (Arjun) has a NULL joining date.
• Two employees joined on 2028-02-29. 2028 is a leap year, so that date is real. 2029 is not a leap year, so 2029-02-29 does not exist.
• Nikhil has two logins with the exact same timestamp.

Syntax

DATE vs DATETIME

SET @date_temp     = DATE('2029-08-19');                    -- date only
SET @datetime_temp = TIMESTAMP('2029-05-18 12:50:45');      -- date and time

• @date_temp holds only a date: YYYY-MM-DD.
• @datetime_temp holds both date and time: YYYY-MM-DD HH:MM:SS.

Mental Model

A date or a date-time is like a label made of small pieces. SQL gives you a function for each piece.

  2029-05-18 12:50:45
  |    |  |  |  |  |
  |    |  |  |  |  +-- SECOND()  = 45
  |    |  |  |  +----- MINUTE()  = 50
  |    |  |  +-------- HOUR()    = 12
  |    |  +----------- DAY()     = 18
  |    +-------------- MONTH()    = 5
  +------------------- YEAR()     = 2029

A date-time is also a point on a line. A date range is a piece of that line, and the cleanest way to describe a piece is "from here, up to (but not including) there":

  Jan 1, 00:00:00                                  Feb 1, 00:00:00
  [======================== January ======================)
   included                                        not included

How SQL Executes It

• When you compare a date column with a text value such as '2029-01-31', MySQL converts the text into a date (or a date-time) first.
• A date given without a time is turned into midnight when compared with a DATETIME column. So '2029-01-31' becomes '2029-01-31 00:00:00'. This one fact causes the most famous date bug (see section 4).
• Date functions such as YEAR() are worked out for each row when they are used in a WHERE clause. That has a cost for indexes (see section 2).
• Functions with a NULL input return NULL, so YEAR(NULL) is NULL, not 0.

1. Extracting Date Parts

Method 1: Direct functions (the most common)

The simplest and most widely used approach. SQL has one function for each part.

SQL Query

SELECT
    YEAR(@date_temp)  AS year,
    MONTH(@date_temp) AS month,
    DAY(@date_temp)   AS day;

Output

• YEAR() extracts the year part: 2029
• MONTH() extracts the month part: 8
• DAY() extracts the day part: 19

Extracting from a DATETIME or TIMESTAMP value

SELECT
    HOUR(@datetime_temp)   AS hour,
    MINUTE(@datetime_temp) AS minute,
    SECOND(@datetime_temp) AS second;

Output

Method 2: The EXTRACT() function

EXTRACT() is part of the SQL standard, so it works across many databases.

SQL Query

SELECT
    EXTRACT(YEAR  FROM @date_temp) AS year,
    EXTRACT(MONTH FROM @date_temp) AS month,
    EXTRACT(DAY   FROM @date_temp) AS day;

How it works You say what to extract (YEAR, MONTH, DAY) and from which date value. The result is the same as Method 1: 2029, 8, 19.

Scenario 1: Extract the year and month from joining dates

SQL Query

SELECT
  emp_id,
  name,
  YEAR(joining_date)  AS join_year,
  MONTH(joining_date) AS join_month
FROM employees;

Output

Key point: YEAR(NULL) and MONTH(NULL) return NULL, not 0. Arjun has no joining date, so both parts are NULL.

Scenario 2: Hiring count by year and month

SQL Query

SELECT
  MONTH(joining_date) AS join_month,
  YEAR(joining_date)  AS join_year,
  COUNT(*)            AS hires
FROM employees
GROUP BY
  YEAR(joining_date),
  MONTH(joining_date)
ORDER BY
  join_month,
  join_year;

Explanation

• MONTH(joining_date) and YEAR(joining_date) break each joining date into two parts.
• GROUP BY YEAR(joining_date), MONTH(joining_date) groups employees who joined in the same year and the same month.
• COUNT(*) counts the employees in each year-month group.
• ORDER BY join_month, join_year sorts first by month, and then by year inside the same month.

 Bucket (year, month)   Employees in it
 (NULL, NULL)           Arjun
 (2028, 1)              Farah
 (2028, 2)              Neha, Zoya
 (2029, 2)              Tara
 (2028, 3)              Aditi
 (2029, 3)              Kiran
 (2028, 11)             Rohan
 (2028, 12)             Ishan, Kabir

Output

Important behavior when joining_date is NULL

• MONTH(NULL) is NULL and YEAR(NULL) is NULL.
• Rows with a NULL joining date form their own group (NULL, NULL).
• COUNT(*) counts those rows, so you may see hires = 1 (or more if several NULLs exist).
• NULL sorts first in an ascending ORDER BY in MySQL.

Why group by both year and month If you group by month alone, March 2028 and March 2029 would be merged into one group. Always include the year when your data spans more than one year.

2. The Big Performance Rule: Do Not Wrap the Column in a Function

Do not filter using YEAR(), MONTH(), or DATE_FORMAT() on the column. If you write this:

WHERE YEAR(joining_date) = 2029
  AND MONTH(joining_date) = 2;

then even if there is an index on joining_date, MySQL may not use it well, because the column is wrapped inside functions. The database has to work out YEAR() and MONTH() for every row, which risks a full table scan.

 Index on joining_date (sorted by the actual date):

 2028-01-01  2028-02-29  2028-02-29  2028-03-15  ...  2029-02-10  2029-03-22

 Range filter  >= '2029-02-01'  AND  < '2029-03-01'
    -> jump straight to 2029-02-01, read until 2029-03-01, stop. FAST.

 YEAR(joining_date) = 2029 AND MONTH(joining_date) = 2
    -> the index is sorted by the date, not by YEAR(date)
    -> compute YEAR() and MONTH() for every row. SLOW on big tables.

The index-friendly way: use date ranges

SELECT emp_id, name, joining_date
FROM employees
WHERE joining_date >= '2029-02-01'
  AND joining_date <  '2029-03-01';

Output

Why it works The column stays "bare" (no function around it), so the index can jump straight to the right place. The range >= first day and < first day of next month also covers every month length (28, 29, 30, or 31 days) without any extra thought.

3. Filtering with Date Ranges

BETWEEN is commonly used to filter inclusive date ranges. x BETWEEN a AND b means x >= a AND x <= b.

Scenario 1: Orders placed in January 2029

SQL Query (with BETWEEN)

SELECT
  order_id,
  customer,
  order_date
FROM orders
WHERE order_date BETWEEN '2029-01-01'
  AND '2029-01-31';

The same query written with comparison operators

SELECT
  order_id,
  customer,
  order_date
FROM orders
WHERE order_date >= '2029-01-01'
  AND order_date <= '2029-01-31';

Output

Why it works Here order_date is a DATE column (no time part), so the last day of January is simply '2029-01-31', and BETWEEN works correctly. The trouble starts when the column is a DATETIME, as the next section shows.

4. The BETWEEN Pitfall with DATETIME (A Classic Interview Trap)

When you filter a date range on a DATETIME column, using BETWEEN with date-only values often returns incorrect results. It is a common interview trap and a real source of bugs.

The problematic query

WHERE login_time BETWEEN '2029-01-01' AND '2029-01-31'

At first glance, this looks like it fetches all logins in January 2029. It does not.

What actually happens

• BETWEEN is inclusive on both ends: x BETWEEN a AND b is the same as x >= a AND x <= b.
• login_time is a DATETIME, but the two values are date-only.
• SQL turns each date-only value into a DATETIME by adding midnight:
  • '2029-01-01' becomes '2029-01-01 00:00:00'
  • '2029-01-31' becomes '2029-01-31 00:00:00'

So the query is really:

WHERE login_time >= '2029-01-01 00:00:00'
  AND login_time <= '2029-01-31 00:00:00';

 Jan 1 00:00:00                      Jan 31 00:00:00      Feb 1 00:00:00
 [=================== included =================]<-- cut off here
                                                |xxxxxxxxxxxxxxxxxx|
                                     Jan 31 09:45:00, 18:30:12, 23:59:59
                                     are all MISSING

Only the very first second of January 31 is included. Any login later that day is excluded, for example 09:45:00, 18:30:12, or 23:59:59.

See it with real data

Logins in January 2029 are Ashwin (Jan 15), Simran (Jan 20), and Isha (Jan 31 at 10:30).

The correct and recommended pattern: a half-open interval

Use an inclusive start and an exclusive end:

WHERE login_time >= '2029-01-01 00:00:00'
  AND login_time <  '2029-02-01 00:00:00'

Why this pattern is correct

• It includes every possible moment in January.
• It works safely with fractional seconds (DATETIME(3), DATETIME(6)).
• It avoids guessing the last second of the day (23:59:59).
• It is more index-friendly and predictable.
• It scales cleanly to day, month, and year ranges.

This is called a half-open interval: [start_date, next_period_start).

Why 23:59:59 is not reliable

AND login_time <= '2029-01-31 23:59:59'

This fails if the column stores fractions of a second. A value like 2029-01-31 23:59:59.500 is later than 23:59:59, so it would be excluded. This pattern is not production-safe.

Rule: for DATETIME columns, avoid BETWEEN with date-only values. Always filter with >= start and < next_period_start.

5. Finding First and Latest Events

MIN and MAX work on dates and times too. They find the earliest and the latest moment.

Scenario 1: Each user's first and latest login time

SQL Query

SELECT
  `user`,
  MIN(login_time) AS first_login,
  MAX(login_time) AS latest_login
FROM logins
GROUP BY `user`
ORDER BY `user`;

(user is written with backticks because USER is also the name of a MySQL function.)

Output

Why it works For a date or time, "smallest" means earliest and "largest" means latest. Users with only one login show the same value in both columns.

Scenario 2: Count of logins (with first and latest)

SQL Query

SELECT
  `user`,
  MIN(login_time) AS first_login_time,
  MAX(login_time) AS latest_login_time,
  COUNT(*) AS number_of_logins
FROM logins
GROUP BY `user`
ORDER BY `user`;

Output

Why it works Nikhil has 2 logins at the exact same moment, so his first and latest times are equal, but his count is still 2. COUNT(*) counts rows, and MIN/MAX look at values.

6. Calculating Date Differences

DATEDIFF (difference in days)

DATEDIFF(end_date, start_date) returns the number of days between two dates. It compares only the date part and ignores the time.

SQL Query

SET @date_1 = '2029-01-10';
SET @date_2 = '2029-07-19';
SELECT DATEDIFF(@date_2, @date_1) AS days_diff;

Output

Note: DATEDIFF is always in days. Even if you store timestamps, it effectively compares only the dates.

TIMESTAMPDIFF (difference in any unit)

TIMESTAMPDIFF(unit, start_datetime, end_datetime) returns the difference between two date-times in the unit you choose (HOUR, MINUTE, DAY, and so on). It uses both date and time.

SQL Query

SET @date_ts_1 = TIMESTAMP('2029-01-10 19:07:10');
SET @date_ts_2 = TIMESTAMP('2029-09-10 12:07:10');
SELECT TIMESTAMPDIFF(HOUR, @date_ts_1, @date_ts_2) AS hours_diff;

Output

Note: TIMESTAMPDIFF returns the number of whole units between the two moments. For HOUR, it counts complete hours only and never returns decimals.

The two functions take their arguments in a different order

 DATEDIFF(end_date, start_date)                 end FIRST, then start
 TIMESTAMPDIFF(unit, start_datetime, end_datetime)   unit, then start, then end

Mixing them up gives a negative number. This is a common mistake.

Scenario: Days since each support ticket was opened

SQL Query

SELECT
  ticket_id,
  customer,
  DATEDIFF(CURRENT_DATE, opened_on) AS days_open
FROM support_tickets
WHERE status = 'OPEN';

Output (the sample run, assuming CURRENT_DATE = 2026-02-01)

Why are the numbers negative? DATEDIFF(end, start) is "end minus start". In this sample run, the assumed CURRENT_DATE (2026-02-01) is earlier than the dates the tickets were opened (in 2029), so the result is negative. In real use, CURRENT_DATE comes after opened_on, and the number is positive.

A realistic example (extra) Suppose CURRENT_DATE = '2029-02-10':

A ticket opened today has 0 days open.

7. Using the Current Date and Time

CURRENT_DATE and CURRENT_TIMESTAMP give the current system date and time.

Scenario: Find users who logged in today

SQL Query

SELECT
  login_id, `user`, login_time
FROM logins
WHERE login_time >= TIMESTAMP(CURRENT_DATE, '00:00:00')
  AND login_time <  TIMESTAMP(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY), '00:00:00')
ORDER BY login_time, login_id;

Explanation

• TIMESTAMP(CURRENT_DATE, '00:00:00') is today at midnight (the start of the day).
• DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) is tomorrow's date, and TIMESTAMP(..., '00:00:00') makes it tomorrow at midnight.
• The filter is the half-open interval you learned in section 4: from today's midnight, up to (but not including) tomorrow's midnight.

Output (with CURRENT_DATE = '2029-02-10')

Why it works Midnight logins (Nikhil) are included, and so is the 23:59:59 login (Priya). Priya's login on Feb 9 at 23:59:59 and Asha's login on Feb 11 at 00:00:00 are correctly left out.

A shorter way to write the same filter (extra)

WHERE login_time >= CURRENT_DATE
  AND login_time <  CURRENT_DATE + INTERVAL 1 DAY

It returns the same six rows.

8. Food Delivery Latency

Time differences are commonly used to measure delivery performance.

Scenario 1: Delivery time in minutes, and flag slow deliveries

SQL Query

SELECT
  order_id,
  order_placed_at,
  delivered_at,
  TIMESTAMPDIFF(MINUTE, order_placed_at, delivered_at) AS delivery_minutes,
  CASE
    WHEN delivered_at IS NULL THEN 'Pending'
    WHEN delivered_at < order_placed_at THEN 'Bad Data'
    WHEN TIMESTAMPDIFF(SECOND, order_placed_at, delivered_at) <= 60 * 30 THEN 'On Time'
    ELSE 'Slow'
  END AS delivery_status
FROM deliveries;

The CASE works as a checklist, top to bottom (first match wins)

 delivered_at is NULL?                       -> Pending   (not delivered yet)
        | no
 delivered_at earlier than order_placed_at?  -> Bad Data  (impossible)
        | no
 took 30 minutes (1800 seconds) or less?     -> On Time
        | no
                                             -> Slow

Output

Why it works

• Order of the WHEN checks matters. NULL is checked first, because any comparison with NULL is UNKNOWN. "Bad Data" is checked before "On Time", or a negative time (-10 minutes) would wrongly count as "on time".
• Why seconds for the limit? TIMESTAMPDIFF(MINUTE, ...) returns whole minutes and cuts off the extra seconds. A delivery of 30 minutes 59 seconds would show as 30 minutes and pass a <= 30 minute check, even though it is late. Measuring in seconds (<= 60 * 30) is exact.
• Order 9006 took exactly 30 minutes (1800 seconds), which is on the limit, so it is "On Time". Order 9005 took 31 minutes, so it is "Slow".
• Orders 9007 and 9008 cross midnight, and the time difference still works, because the function uses both date and time.

Scenario 2: Orders per month

SQL Query

SELECT
  MONTH(order_placed_at) AS month,
  YEAR(order_placed_at)  AS year,
  COUNT(*)               AS total_orders
FROM deliveries
GROUP BY
  MONTH(order_placed_at),
  YEAR(order_placed_at);

Output

Why it works All 10 orders were placed in February 2029 (between Feb 8 and Feb 11), so they fall in a single year-month group.

9. Other Handy Date Functions (extra)

A warning about date + 1 In MySQL, writing recordDate + 1 does not add one day. It turns the date into a number (like 20230601) and adds 1, which breaks at the end of a month. Always use DATE_ADD(recordDate, INTERVAL 1 DAY).

Deep Dive

Internal behavior A DATE is stored compactly, and a DATETIME is stored as a date plus a time. Comparisons are done on the values, not on text, so '2029-02-01' < '2029-10-01' compares real dates (text comparison would be unsafe for other formats). Keep dates in a DATE or DATETIME column, not in text columns.

NULL handling

Performance considerations

• Keep the date column bare on one side of the comparison (col >= '2029-02-01'), so an index can be used.
• Avoid WHERE YEAR(col) = ..., WHERE MONTH(col) = ..., and WHERE DATE_FORMAT(col, ...) = ... on large tables.
• From MySQL 8.0.13, you can create a functional index on an expression (for example, on YEAR(col)) if you really need to filter that way often. A plain range filter is still simpler.
• Range filters that use a half-open interval stay correct as data changes (new time precision, new time zones).

Database behavior

• YEAR(), MONTH(), DAY(), DATEDIFF(), TIMESTAMPDIFF(), and DATE_ADD() are MySQL functions. EXTRACT(part FROM date) and CURRENT_DATE follow the SQL standard and work in more databases.
• Other databases use different names: for example, PostgreSQL uses date2 - date1 for days and AGE() for intervals, and SQL Server uses DATEDIFF(unit, start, end) with a different argument order.
• Time zones: CURRENT_TIMESTAMP returns the server or session time zone. If users are in many time zones, decide whether to store UTC.

Real-world Use Cases

• Monthly and yearly reports: orders, signups, or hires per month (GROUP BY year and month).
• Date-range reports: sales in a quarter, tickets opened last week (half-open intervals).
• Activity dashboards: first login, latest login, and number of logins per user (MIN, MAX, COUNT).
• Aging reports: how many days a ticket or invoice has been open (DATEDIFF).
• Service-level checks: deliveries longer than 30 minutes, tickets answered within 2 hours (TIMESTAMPDIFF plus CASE).
• "Today" and "last 7 days" filters: dashboards that use CURRENT_DATE.
• Data quality checks: rows where the end time is before the start time ("Bad Data").

Review Tips

Interviewers commonly ask:

• "Why is WHERE login_time BETWEEN '2029-01-01' AND '2029-01-31' wrong for a DATETIME column?" — The end value becomes midnight of Jan 31, so the rest of that day is missed. Use >= start AND < next_period_start.
• "Why should you avoid WHERE YEAR(col) = 2029 on a big table?" — The function on the column prevents efficient index use. Use a date range instead.
• "What is the difference between DATEDIFF and TIMESTAMPDIFF?" — DATEDIFF returns days and ignores time. TIMESTAMPDIFF returns whole units (hours, minutes, ...) and uses the time.
• "What is the argument order for each?" — DATEDIFF(end, start) and TIMESTAMPDIFF(unit, start, end).
• "What does YEAR(NULL) return?" — NULL, not 0.
• "How do you find the first and last login of each user?" — GROUP BY user with MIN and MAX.
• "Why measure a late delivery in seconds and not minutes?" — TIMESTAMPDIFF(MINUTE, ...) drops the extra seconds.
• "What is a half-open interval?" — [start, next_start): includes the start, excludes the end.

Common Mistakes to Avoid

Mistake 1: Using BETWEEN with date-only values on a DATETIME column

Why it happens: It works on DATE columns, so it feels safe everywhere.

Wrong:

WHERE login_time BETWEEN '2029-01-01' AND '2029-01-31'

Logins after midnight on Jan 31 are missed (Isha's 10:30 login).

Correct:

WHERE login_time >= '2029-01-01' AND login_time < '2029-02-01'

Mistake 2: Using 23:59:59 as the end of the day

Why it happens: It looks like "the last second".

Wrong:

AND login_time <= '2029-01-31 23:59:59'

A value like 23:59:59.500 is missed when fractional seconds are stored.

Correct: Use < next day 00:00:00.

Mistake 3: Wrapping an indexed date column in a function

Why it happens: YEAR(col) = 2029 reads naturally.

Wrong:

WHERE YEAR(joining_date) = 2029 AND MONTH(joining_date) = 2

Possible full table scan.

Correct:

WHERE joining_date >= '2029-02-01' AND joining_date < '2029-03-01'

Mistake 4: Grouping by month without the year

Why it happens: "Orders per month" sounds like only the month matters.

Wrong:

GROUP BY MONTH(order_date)

March 2028 and March 2029 are merged into one group.

Correct: GROUP BY YEAR(order_date), MONTH(order_date).

Mistake 5: Mixing up the argument order of DATEDIFF and TIMESTAMPDIFF

Why it happens: The two functions look alike.

Wrong:

DATEDIFF(opened_on, CURRENT_DATE)       -- start first: the result is negative
TIMESTAMPDIFF(delivered_at, order_placed_at, MINUTE)   -- wrong order

Correct: DATEDIFF(CURRENT_DATE, opened_on) and TIMESTAMPDIFF(MINUTE, order_placed_at, delivered_at).

Mistake 6: Using DATEDIFF when the time matters

Why it happens: DATEDIFF is the first difference function people learn.

Why it fails: DATEDIFF('2029-02-11 00:05:00', '2029-02-10 23:55:00') is 1 day, although only 10 minutes passed.

Correct: Use TIMESTAMPDIFF with the right unit.

Mistake 7: Expecting TIMESTAMPDIFF to return decimals

Why it happens: 90 minutes "is 1.5 hours".

Why it fails: TIMESTAMPDIFF(HOUR, ...) returns whole hours (1), not 1.5.

Correct: Measure in a smaller unit (MINUTE) and divide if you need decimals.

Mistake 8: Forgetting that NULL dates need their own rule

Why it happens: Test data usually has no NULLs.

Why it hurts: YEAR(NULL) is NULL, groups with NULL dates appear, and undelivered orders have a NULL difference.

Correct: Handle NULL on purpose (for example, the first WHEN of a CASE checks IS NULL).

Mistake 9: Using date + 1 to add a day

Why it happens: It works in some other languages.

Why it fails: In MySQL it adds 1 to a number like 20230630, giving 20230631.

Correct: DATE_ADD(date, INTERVAL 1 DAY).

Key Takeaways

• Use YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), and SECOND() (or EXTRACT) to take a date apart. Functions on NULL return NULL.
• Group by both year and month when data spans several years.
• Do not wrap an indexed date column in a function inside WHERE. Use date ranges.
• A date-only value compared with a DATETIME becomes midnight of that day. That is why BETWEEN with date-only values misses most of the last day.
• Use a half-open interval for ranges: >= start and < next_period_start.
• Do not use 23:59:59 as the end of the day.
• MIN and MAX give the earliest and latest date or time.
• DATEDIFF(end, start) gives days and ignores time. TIMESTAMPDIFF(unit, start, end) gives whole units and uses time.
• TIMESTAMPDIFF returns whole units only, so measure in seconds when exactness matters.
• In a CASE on times, check NULL first, then bad data, then the business rule.
• Use DATE_ADD(date, INTERVAL n unit) to add time. Never use date + 1.