DATEDIFF and TIMESTAMPDIFF in MySQL - Mini Project: Build & Apply

Calculate Date Differences with DATEDIFF and TIMESTAMPDIFF in MySQL

📖 Scenario: You work in a small library that tracks when books are borrowed and returned. You want to find out how many days each book was borrowed and also calculate the difference in months between borrow dates and today.

🎯 Goal: Build SQL queries using DATEDIFF and TIMESTAMPDIFF functions to calculate the number of days between two dates and the number of months between a borrow date and the current date.

📋 What You'll Learn

Create a table called borrow_records with columns book_id, borrow_date, and return_date.

Insert three records with exact dates for borrow and return.

Write a query using DATEDIFF to find the number of days each book was borrowed.

Write a query using TIMESTAMPDIFF to find the number of months between the borrow date and today.

💡 Why This Matters

🌍 Real World

Libraries, rental services, and any system that tracks borrowing or usage periods need to calculate date differences to manage returns and analyze usage.

💼 Career

Database developers and analysts often write queries to calculate durations between dates for reporting, billing, or operational decisions.

1

Create the borrow_records table and insert data

Create a table called borrow_records with columns book_id as INT, borrow_date as DATE, and return_date as DATE. Then insert these exact records: (1, '2024-01-10', '2024-01-20'), (2, '2024-02-15', '2024-02-25'), and (3, '2024-03-01', '2024-03-10').

MySQL

-- Create the table and insert the records
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the records with exact dates.

2

Set up a variable for today's date

Create a variable called @today and set it to the current date using CURDATE().

MySQL

CREATE TABLE borrow_records (
  book_id INT,
  borrow_date DATE,
  return_date DATE
);

INSERT INTO borrow_records (book_id, borrow_date, return_date) VALUES
(1, '2024-01-10', '2024-01-20'),
(2, '2024-02-15', '2024-02-25'),
(3, '2024-03-01', '2024-03-10');

-- Set the variable @today to the current date
-- Your code here

Hint

Use SET @today = CURDATE() to store today's date in a variable.

3

Calculate days borrowed using DATEDIFF

Write a query that selects book_id and the number of days borrowed as days_borrowed using DATEDIFF(return_date, borrow_date) from the borrow_records table.

MySQL

CREATE TABLE borrow_records (
  book_id INT,
  borrow_date DATE,
  return_date DATE
);

INSERT INTO borrow_records (book_id, borrow_date, return_date) VALUES
(1, '2024-01-10', '2024-01-20'),
(2, '2024-02-15', '2024-02-25'),
(3, '2024-03-01', '2024-03-10');

SET @today = CURDATE();

-- Select book_id and days borrowed using DATEDIFF
-- Your code here

Hint

Use SELECT book_id, DATEDIFF(return_date, borrow_date) AS days_borrowed FROM borrow_records; to get the days each book was borrowed.

4

Calculate months since borrow date using TIMESTAMPDIFF

Write a query that selects book_id and the number of months since the borrow date as months_since_borrow using TIMESTAMPDIFF(MONTH, borrow_date, @today) from the borrow_records table.

MySQL

CREATE TABLE borrow_records (
  book_id INT,
  borrow_date DATE,
  return_date DATE
);

INSERT INTO borrow_records (book_id, borrow_date, return_date) VALUES
(1, '2024-01-10', '2024-01-20'),
(2, '2024-02-15', '2024-02-25'),
(3, '2024-03-01', '2024-03-10');

SET @today = CURDATE();

SELECT book_id, DATEDIFF(return_date, borrow_date) AS days_borrowed FROM borrow_records;

-- Select book_id and months since borrow using TIMESTAMPDIFF
-- Your code here

Hint

Use TIMESTAMPDIFF(MONTH, borrow_date, @today) to calculate months since borrow date.

Practice

1. What does the MySQL function DATEDIFF(date1, date2) return?

easy

A. The number of days between date1 and date2

B. The number of seconds between date1 and date2

C. The difference in months between date1 and date2

D. The current date and time

Solution

1. Step 1: Understand the function purpose

   DATEDIFF calculates the difference between two dates in days only.

2. Step 2: Confirm the output type

   The output is an integer representing how many days separate the two dates.

3. Final Answer:

   The number of days between date1 and date2 -> Option A

4. Quick Check:

   DATEDIFF returns days difference [OK]

Hint: DATEDIFF always returns days difference between two dates [OK]

Common Mistakes:

• Thinking it returns seconds or months
• Confusing order of dates for output sign
• Using it to get current date/time

2. Which of the following is the correct syntax to find the difference in hours between two datetime values start_time and end_time using TIMESTAMPDIFF?

easy

A. TIMESTAMPDIFF(HOUR, start_time, end_time)

B. TIMESTAMPDIFF(start_time, end_time, HOUR)

C. TIMESTAMPDIFF('HOUR', end_time, start_time)

D. TIMESTAMPDIFF(end_time, start_time, 'HOUR')

Solution

1. Step 1: Recall TIMESTAMPDIFF syntax

   The correct syntax is TIMESTAMPDIFF(unit, datetime1, datetime2) where unit is a keyword like HOUR without quotes.

2. Step 2: Match parameters order

   Unit first, then start_time, then end_time is correct.

3. Final Answer:

   TIMESTAMPDIFF(HOUR, start_time, end_time) -> Option A

4. Quick Check:

   Unit first, then start, then end [OK]

Hint: TIMESTAMPDIFF(unit, start, end) with unit unquoted [OK]

Common Mistakes:

• Putting unit last or in quotes
• Swapping start and end parameters
• Using lowercase unit in quotes

3. Given the query:
SELECT TIMESTAMPDIFF(DAY, '2024-01-01', '2024-01-10') AS diff_days;
What will be the output of diff_days?

medium

A. Error

B. 10

C. 11

D. 9

Solution

1. Step 1: Understand TIMESTAMPDIFF with DAY unit

   It counts full days between the two dates, excluding the start date.

2. Step 2: Calculate days between 2024-01-01 and 2024-01-10

   From Jan 1 to Jan 10 is 9 full days difference.

3. Final Answer:

   9 -> Option D

4. Quick Check:

   10 - 1 = 9 days difference [OK]

Hint: TIMESTAMPDIFF counts full units between dates [OK]

Common Mistakes:

• Counting both start and end dates
• Expecting 10 instead of 9
• Confusing with DATEDIFF behavior

4. Identify the error in this query:
SELECT TIMESTAMPDIFF('MINUTE', '2024-04-01 10:00:00', '2024-04-01 11:30:00');

medium

A. TIMESTAMPDIFF cannot use MINUTE as unit

B. Dates must be swapped in order

C. Unit 'MINUTE' should not be in quotes

D. Dates must be in DATE format, not datetime

Solution

1. Step 1: Check TIMESTAMPDIFF unit syntax

   The unit keyword like MINUTE must be unquoted, as a keyword, not a string.

2. Step 2: Validate other parts

   The dates and order are correct; MINUTE is a valid unit; datetime format is allowed.

3. Final Answer:

   Unit 'MINUTE' should not be in quotes -> Option C

4. Quick Check:

   Unit keywords are unquoted [OK]

Hint: Remove quotes around unit in TIMESTAMPDIFF [OK]

Common Mistakes:

• Quoting the unit keyword
• Swapping date order unnecessarily
• Thinking datetime is invalid input

5. You want to find the total number of months between two dates '2023-01-15' and '2024-06-10', but only count full months passed. Which query correctly does this?

hard

A. SELECT DATEDIFF('2024-06-10', '2023-01-15') / 30;

B. SELECT TIMESTAMPDIFF(MONTH, '2023-01-15', '2024-06-10');

C. SELECT TIMESTAMPDIFF(DAY, '2023-01-15', '2024-06-10') / 30;

D. SELECT TIMESTAMPDIFF(YEAR, '2023-01-15', '2024-06-10') * 12;

Solution

1. Step 1: Understand requirement for full months

   We need full months difference, not approximate days divided by 30.

2. Step 2: Check each option

   SELECT TIMESTAMPDIFF(MONTH, '2023-01-15', '2024-06-10'); uses TIMESTAMPDIFF with MONTH unit, which counts full months correctly.
   SELECT DATEDIFF('2024-06-10', '2023-01-15') / 30; and C use days divided by 30, which is approximate and can be wrong.
   SELECT TIMESTAMPDIFF(YEAR, '2023-01-15', '2024-06-10') * 12; counts years times 12, ignoring leftover months.

3. Final Answer:

   SELECT TIMESTAMPDIFF(MONTH, '2023-01-15', '2024-06-10'); -> Option B

4. Quick Check:

   Use TIMESTAMPDIFF with MONTH for full months [OK]

Hint: Use TIMESTAMPDIFF with MONTH for full months difference [OK]

Common Mistakes:

• Dividing days by 30 for months
• Using YEAR unit and multiplying
• Using DATEDIFF which returns days only