Date arithmetic with intervals in PostgreSQL - Mini Project: Build & Apply

Date Arithmetic with Intervals in PostgreSQL

📖 Scenario: You are managing a small library database. You want to calculate due dates for borrowed books by adding a fixed loan period to the borrow date.

🎯 Goal: Build a SQL query that adds an interval of days to a borrow date to find the due date for returning books.

📋 What You'll Learn

Create a table named borrowed_books with columns book_id (integer) and borrow_date (date).

Insert three rows with specific book_id and borrow_date values.

Define a variable or use a fixed interval of 14 days representing the loan period.

Write a SELECT query that shows book_id, borrow_date, and a new column due_date which is borrow_date plus the 14-day interval.

💡 Why This Matters

🌍 Real World

Libraries, rental services, and booking systems often need to calculate due dates by adding fixed time intervals to start dates.

💼 Career

Understanding date arithmetic with intervals is essential for database developers and analysts working with time-based data.

1

Create the borrowed_books table and insert data

Create a table called borrowed_books with columns book_id as integer and borrow_date as date. Insert these exact rows: (1, '2024-06-01'), (2, '2024-06-05'), and (3, '2024-06-10').

PostgreSQL

-- Create table borrowed_books and insert 3 rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the loan period interval

Define a variable or use a fixed interval of 14 days named loan_period using PostgreSQL interval syntax.

PostgreSQL

CREATE TABLE borrowed_books (
  book_id INTEGER,
  borrow_date DATE
);

INSERT INTO borrowed_books (book_id, borrow_date) VALUES
  (1, '2024-06-01'),
  (2, '2024-06-05'),
  (3, '2024-06-10');

-- Define loan_period as interval '14 days'
-- Your code here

Need a hint?

Use \set loan_period '14 days'::interval in psql or assign interval '14 days' in a query.

3

Write a SELECT query to calculate due dates

Write a SELECT query that shows book_id, borrow_date, and a new column due_date which is borrow_date plus the interval interval '14 days'.

PostgreSQL

CREATE TABLE borrowed_books (
  book_id INTEGER,
  borrow_date DATE
);

INSERT INTO borrowed_books (book_id, borrow_date) VALUES
  (1, '2024-06-01'),
  (2, '2024-06-05'),
  (3, '2024-06-10');

\set loan_period '14 days'::interval

-- Write SELECT query to show book_id, borrow_date, and due_date as borrow_date + interval '14 days'
-- Your code here

Need a hint?

Use borrow_date + interval '14 days' to add days to the date.

4

Use the variable loan_period in the SELECT query

Modify the SELECT query to use the variable loan_period instead of the fixed interval interval '14 days' to calculate due_date.

PostgreSQL

CREATE TABLE borrowed_books (
  book_id INTEGER,
  borrow_date DATE
);

INSERT INTO borrowed_books (book_id, borrow_date) VALUES
  (1, '2024-06-01'),
  (2, '2024-06-05'),
  (3, '2024-06-10');

\set loan_period '14 days'::interval

-- Modify SELECT query to use :loan_period variable for due_date calculation
-- Your code here

Need a hint?

Use :loan_period to refer to the variable in the query.