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

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

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

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

Hint

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

Practice

1. What does the following PostgreSQL expression do?
SELECT CURRENT_DATE + INTERVAL '5 days';

easy

A. Returns the current date without change

B. Subtracts 5 days from the current date

C. Causes a syntax error

D. Adds 5 days to the current date

Solution

1. Step 1: Understand the CURRENT_DATE keyword

   CURRENT_DATE returns today's date without time.

2. Step 2: Add interval of 5 days

   Adding INTERVAL '5 days' increases the date by 5 days.

3. Final Answer:

   Adds 5 days to the current date -> Option D

4. Quick Check:

   Adding interval '5 days' = date + 5 days [OK]

Hint: Adding INTERVAL 'X days' shifts date forward by X days [OK]

Common Mistakes:

• Confusing addition with subtraction
• Thinking CURRENT_DATE includes time
• Using wrong interval syntax

2. Which of the following is the correct syntax to subtract 3 months from a date column named order_date?

easy

A. order_date - '3 months'

B. order_date - INTERVAL '3 months'

C. order_date - INTERVAL 3 months

D. order_date - INTERVAL '3 month'

Solution

1. Step 1: Identify correct interval syntax

   INTERVAL must be followed by a quoted string with number and unit, e.g., '3 months'.

2. Step 2: Check subtraction from date column

   Subtracting INTERVAL '3 months' from order_date is valid syntax.

3. Final Answer:

   order_date - INTERVAL '3 months' -> Option B

4. Quick Check:

   Subtract INTERVAL '3 months' with quotes [OK]

Hint: Always quote interval value like '3 months' [OK]

Common Mistakes:

• Omitting quotes around interval string
• Using singular 'month' instead of plural 'months'
• Trying to subtract string without INTERVAL keyword

3. What is the result of this query if today is 2024-06-15?

SELECT (DATE '2024-06-15' + INTERVAL '1 month' - INTERVAL '10 days')::date AS new_date;

medium

A. 2024-07-25

B. 2024-05-25

C. 2024-07-05

D. 2024-06-05

Solution

1. Step 1: Add 1 month to 2024-06-15

   Adding 1 month results in 2024-07-15.

2. Step 2: Subtract 10 days from 2024-07-15

   Subtracting 10 days gives 2024-07-05.

3. Final Answer:

   2024-07-05 -> Option C

4. Quick Check:

   2024-06-15 + 1 month - 10 days = 2024-07-05 [OK]

Hint: Add then subtract intervals stepwise for correct date [OK]

Common Mistakes:

• Subtracting before adding
• Confusing day and month units
• Not casting to date and including time

4. Identify the error in this query:

SELECT CURRENT_DATE + INTERVAL 5 days;

medium

A. Missing quotes around interval value

B. CURRENT_DATE cannot be used with intervals

C. INTERVAL keyword is misspelled

D. Adding intervals to dates is not allowed

Solution

1. Step 1: Check INTERVAL syntax

   INTERVAL requires the value to be a quoted string, e.g., '5 days'.

2. Step 2: Identify missing quotes error

   The query lacks quotes around 5 days, causing syntax error.

3. Final Answer:

   Missing quotes around interval value -> Option A

4. Quick Check:

   INTERVAL '5 days' needs quotes [OK]

Hint: Always put interval value in single quotes [OK]

Common Mistakes:

• Writing INTERVAL 5 days without quotes
• Assuming CURRENT_DATE can't be used with intervals
• Confusing INTERVAL keyword spelling

5. You want to find all orders from a table orders where the order_date is within the last 3 months from today. Which query correctly uses date arithmetic with intervals?

hard

A. SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '3 months';

B. SELECT * FROM orders WHERE order_date <= CURRENT_DATE + INTERVAL '3 months';

C. SELECT * FROM orders WHERE order_date > CURRENT_DATE + INTERVAL '3 months';

D. SELECT * FROM orders WHERE order_date < CURRENT_DATE - INTERVAL '3 months';

Solution

1. Step 1: Understand the requirement

   We want orders from the last 3 months, so order_date should be after (or equal to) 3 months ago.

2. Step 2: Use CURRENT_DATE minus 3 months

   Subtracting INTERVAL '3 months' from CURRENT_DATE gives the date 3 months ago.

3. Step 3: Write condition for order_date

   order_date >= CURRENT_DATE - INTERVAL '3 months' selects orders within last 3 months.

4. Final Answer:

   SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '3 months'; -> Option A

5. Quick Check:

   Filter dates after CURRENT_DATE - 3 months [OK]

Hint: Use >= CURRENT_DATE - INTERVAL 'X months' for recent dates [OK]

Common Mistakes:

• Using + INTERVAL instead of - INTERVAL
• Using < instead of >= for filtering recent dates
• Confusing direction of date comparison