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

Need a 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

Need a 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

Need a 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

Need a hint?

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