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

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

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

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

Need a hint?

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