Why date handling matters in PostgreSQL - See It in Action

Why date handling matters in PostgreSQL

📖 Scenario: You are managing a small library database. You need to store book borrowing dates and calculate due dates correctly. Handling dates properly is important to avoid errors like overdue books or wrong return dates.

🎯 Goal: Build a simple PostgreSQL table to store book borrowings with correct date columns, add a configuration for loan period, calculate due dates using date arithmetic, and finalize the table with constraints to ensure date validity.

📋 What You'll Learn

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

Add a configuration variable loan_period_days to set the loan duration in days

Write a query to select book_id, borrow_date, and calculate due_date by adding loan_period_days to borrow_date

Add a constraint to ensure borrow_date is not in the future

💡 Why This Matters

🌍 Real World

Libraries, rental services, and any system tracking borrowed items need accurate date handling to manage due dates and avoid errors.

💼 Career

Database developers and administrators must handle dates correctly to ensure data integrity and support business rules involving time.

1

Create the borrowings table

Create a table called borrowings with two columns: book_id as integer and borrow_date as date.

PostgreSQL

-- Your code here to create the borrowings table

Need a hint?

Use CREATE TABLE statement with the specified columns and types.

2

Add loan period configuration

Create a variable called loan_period_days and set it to 14 to represent a 14-day loan period.

PostgreSQL

CREATE TABLE borrowings (
    book_id INTEGER,
    borrow_date DATE
);
-- Your code here to set loan_period_days

Need a hint?

Use the \set command in psql to define a variable.

3

Calculate due dates

Write a SELECT query to get book_id, borrow_date, and calculate due_date by adding loan_period_days days to borrow_date. Use the variable loan_period_days in the query.

PostgreSQL

CREATE TABLE borrowings (
    book_id INTEGER,
    borrow_date DATE
);

\set loan_period_days 14

-- Your code here to select book_id, borrow_date, and due_date

Need a hint?

Use INTERVAL with the variable :loan_period_days to add days.

4

Add constraint for borrow_date

Alter the borrowings table to add a CHECK constraint named borrow_date_not_future that ensures borrow_date is less than or equal to the current date (CURRENT_DATE).

PostgreSQL

CREATE TABLE borrowings (
    book_id INTEGER,
    borrow_date DATE
);

\set loan_period_days 14

SELECT book_id, borrow_date, borrow_date + (:loan_period_days || ' days')::interval AS due_date
FROM borrowings;

-- Your code here to add the CHECK constraint

Need a hint?

Use ALTER TABLE with ADD CONSTRAINT and a CHECK condition.