Interval type for durations in PostgreSQL - Mini Project: Build & Apply

Interval Type for Durations in PostgreSQL

📖 Scenario: You are managing a project timeline database. You want to store durations of tasks using PostgreSQL's INTERVAL type to represent time spans like days, hours, and minutes.

🎯 Goal: Create a table to store task names and their durations using the INTERVAL type. Insert sample data with specific durations, then query the table to find tasks longer than a certain duration.

📋 What You'll Learn

Create a table named tasks with columns task_name (text) and duration (INTERVAL).

Insert three tasks with durations: 'Design' - 3 days, 'Development' - 5 days 4 hours, 'Testing' - 2 days 6 hours.

Create a variable or use a value to represent a duration threshold of 3 days.

Write a query to select task names and durations where the duration is greater than the threshold.

💡 Why This Matters

🌍 Real World

Managing project timelines and durations in databases is common in software development, event planning, and operations.

💼 Career

Understanding how to use INTERVAL types helps database administrators and developers handle time durations efficiently.

1

Create the tasks table with interval duration

Create a table called tasks with two columns: task_name of type TEXT and duration of type INTERVAL.

PostgreSQL

-- Create the tasks table with task_name and duration columns
-- Your code here

Hint

Use CREATE TABLE statement with the specified column names and types.

2

Insert tasks with specific durations

Insert three rows into the tasks table with these exact values: 'Design' with duration '3 days', 'Development' with duration '5 days 4 hours', and 'Testing' with duration '2 days 6 hours'. Use the INTERVAL keyword for durations.

PostgreSQL

CREATE TABLE tasks (
    task_name TEXT,
    duration INTERVAL
);
-- Insert the three tasks with their durations below
-- Your code here

Hint

Use INSERT INTO with multiple rows and INTERVAL literals for durations.

3

Set a duration threshold variable

Create a variable called threshold and set it to the interval value '3 days' using the INTERVAL keyword.

PostgreSQL

CREATE TABLE tasks (
    task_name TEXT,
    duration INTERVAL
);

INSERT INTO tasks (task_name, duration) VALUES
('Design', INTERVAL '3 days'),
('Development', INTERVAL '5 days 4 hours'),
('Testing', INTERVAL '2 days 6 hours');

-- Create a variable threshold with INTERVAL '3 days'
-- Your code here

Hint

Use the psql command \set to define a variable for the interval.

4

Query tasks longer than the threshold

Write a SQL query to select task_name and duration from tasks where duration is greater than the variable :threshold.

PostgreSQL

CREATE TABLE tasks (
    task_name TEXT,
    duration INTERVAL
);

INSERT INTO tasks (task_name, duration) VALUES
('Design', INTERVAL '3 days'),
('Development', INTERVAL '5 days 4 hours'),
('Testing', INTERVAL '2 days 6 hours');

\set threshold '3 days'

-- Select task_name and duration where duration > :threshold
-- Your code here

Hint

Use a SELECT statement with a WHERE clause comparing duration to :threshold.

Practice

1. What does the interval type in PostgreSQL represent?

easy

A. A numeric value for counting rows

B. A specific date and time

C. A duration of time like hours, days, or months

D. A text string describing time

Solution

1. Step 1: Understand the interval type purpose

   The interval type is designed to store durations, not fixed points in time.

2. Step 2: Compare with other types

   Unlike timestamp or date types, interval holds a length of time like '3 days' or '2 hours'.

3. Final Answer:

   A duration of time like hours, days, or months -> Option C

4. Quick Check:

   Interval = Duration [OK]

Hint: Interval means duration, not a fixed date/time [OK]

Common Mistakes:

• Confusing interval with timestamp
• Thinking interval stores exact dates
• Assuming interval is a string

2. Which of the following is the correct way to declare an interval of 5 days in PostgreSQL?

easy

A. INTERVAL 5 days

B. INTERVAL '5 days'

C. '5 days' INTERVAL

D. INTERVAL 'days 5'

Solution

1. Step 1: Recall interval literal syntax

   PostgreSQL requires the interval value to be a string inside single quotes after the INTERVAL keyword.

2. Step 2: Check each option

   Only INTERVAL '5 days' matches the correct syntax; others are invalid or reversed.

3. Final Answer:

   INTERVAL '5 days' -> Option B

4. Quick Check:

   Interval syntax = INTERVAL 'value unit' [OK]

Hint: Use INTERVAL followed by quoted string for durations [OK]

Common Mistakes:

• Omitting quotes around the duration
• Placing INTERVAL after the string
• Swapping number and unit order

3. What is the result of this query?

SELECT CURRENT_DATE + INTERVAL '3 days';

medium

A. The current date minus 3 days

B. The current date as text

C. An error because you cannot add interval to date

D. The date 3 days after today

Solution

1. Step 1: Understand adding interval to date

   Adding an interval to a date shifts the date forward by that duration.

2. Step 2: Apply the interval '3 days'

   Adding '3 days' to CURRENT_DATE results in a date 3 days later than today.

3. Final Answer:

   The date 3 days after today -> Option D

4. Quick Check:

   Date + Interval = Date shifted forward [OK]

Hint: Adding interval to date moves date forward [OK]

Common Mistakes:

• Thinking it subtracts days
• Expecting a text output
• Believing it causes an error

4. Identify the error in this query:

SELECT CURRENT_TIMESTAMP + '2 hours';

medium

A. Missing INTERVAL keyword before '2 hours'

B. CURRENT_TIMESTAMP cannot be used with intervals

C. Quotes around '2 hours' should be double quotes

D. No error, query is correct

Solution

1. Step 1: Check interval usage with timestamp

   To add a duration, the value must be an interval type, declared with INTERVAL keyword.

2. Step 2: Analyze the query

   The string '2 hours' lacks INTERVAL keyword, so PostgreSQL will error.

3. Final Answer:

   Missing INTERVAL keyword before '2 hours' -> Option A

4. Quick Check:

   Interval keyword required for durations [OK]

Hint: Always use INTERVAL before quoted duration strings [OK]

Common Mistakes:

• Omitting INTERVAL keyword
• Using wrong quotes
• Assuming timestamp can't add intervals

5. You want to find the date 1 month and 15 days after January 10, 2024. Which query gives the correct result?

hard

A. SELECT DATE '2024-01-10' + INTERVAL '1 month' + INTERVAL '15 days';

B. SELECT DATE '2024-01-10' + INTERVAL '1 month + 15 days';

C. SELECT DATE '2024-01-10' + INTERVAL '45 days';

D. SELECT DATE '2024-01-10' + INTERVAL '1.5 months';

Solution

1. Step 1: Understand interval addition

   Adding intervals separately ensures correct handling of months and days.

2. Step 2: Evaluate each option

   SELECT DATE '2024-01-10' + INTERVAL '1 month + 15 days'; tries to combine units in one string, which is invalid syntax. SELECT DATE '2024-01-10' + INTERVAL '45 days'; adds 45 days, which is not exactly 1 month and 15 days. SELECT DATE '2024-01-10' + INTERVAL '1.5 months'; uses fractional months, which PostgreSQL does not support directly.

3. Final Answer:

   SELECT DATE '2024-01-10' + INTERVAL '1 month' + INTERVAL '15 days'; -> Option A

4. Quick Check:

   Separate intervals for months and days [OK]

Hint: Add months and days as separate intervals for accuracy [OK]

Common Mistakes:

• Combining units in one interval string
• Using fractional months
• Adding days as total instead of separate