Interval type for durations in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate total duration in hours using INTERVAL

Given a table tasks with a column duration of type INTERVAL, what is the total duration in hours for all tasks?

PostgreSQL

SELECT SUM(duration) AS total_duration FROM tasks;

A SELECT SUM(duration)::int AS total_hours FROM tasks;

B SELECT EXTRACT(EPOCH FROM SUM(duration))/3600 AS total_hours FROM tasks;

C SELECT SUM(duration) / INTERVAL '1 hour' AS total_hours FROM tasks;

D SELECT SUM(duration) * 3600 AS total_hours FROM tasks;

💡 Hint

Use EXTRACT with EPOCH to convert INTERVAL to seconds, then convert to hours.

PostgreSQL INTERVAL sums return an INTERVAL type. To get total hours as a number, extract total seconds (EPOCH) and divide by 3600.

📝 Syntax

intermediate

Identify the correct INTERVAL literal syntax

Which of the following is the correct way to write an INTERVAL literal representing 3 days in PostgreSQL?

A '3 days'::INTERVAL

B INTERVAL 3 'days'

C INTERVAL '3 days'

D INTERVAL '3 day'

💡 Hint

INTERVAL literals require the value and unit inside single quotes as one string.

The correct syntax is INTERVAL followed by a single quoted string with number and unit, e.g., INTERVAL '3 days'.

❓ query_result

advanced

Find tasks longer than 1 hour using INTERVAL comparison

Given a table tasks with a column duration of type INTERVAL, which query correctly returns tasks with duration longer than 1 hour?

A SELECT * FROM tasks WHERE duration > INTERVAL '1 hour';

B SELECT * FROM tasks WHERE duration > INTERVAL 1 HOUR;

C SELECT * FROM tasks WHERE duration > 1;

D SELECT * FROM tasks WHERE duration > '1 hour';

💡 Hint

INTERVAL literals must be quoted strings; direct numbers or unquoted units cause errors.

Only option A uses correct INTERVAL syntax for comparison. Option A treats '1 hour' as text, causing error or wrong comparison. Option A compares INTERVAL to integer, invalid. Option A syntax is invalid.

❓ optimization

advanced

Optimize query summing durations grouped by category

You have a tasks table with columns category (text) and duration (INTERVAL). Which query efficiently returns total duration per category?

A SELECT category, SUM(duration) AS total_duration FROM tasks GROUP BY category ORDER BY category;

B SELECT category, SUM(duration)::text AS total_duration FROM tasks GROUP BY category;

C SELECT category, SUM(duration) / INTERVAL '1 second' AS total_duration_seconds FROM tasks GROUP BY category;

D SELECT category, SUM(duration) * INTERVAL '1 second' AS total_duration FROM tasks GROUP BY category;

💡 Hint

Summing INTERVALs directly and grouping by category is efficient and clear.

Option A sums INTERVALs per category correctly and orders results. Option A casts to text unnecessarily. Option A divides INTERVAL by INTERVAL, yielding numeric seconds but less readable. Option A multiplies INTERVAL by INTERVAL, invalid operation.

🔧 Debug

expert

Why does this INTERVAL addition query fail?

Consider the query:
SELECT start_time + '2 hours' FROM events; where start_time is a TIMESTAMP column. Why does this query fail?

A Because TIMESTAMP cannot be added to INTERVAL in PostgreSQL.

B Because addition operator '+' is not supported for TIMESTAMP and INTERVAL.

C Because '2 hours' is a string, it must be cast to INTERVAL explicitly.

D Because the syntax requires INTERVAL keyword before the string literal.

💡 Hint

PostgreSQL requires INTERVAL keyword before interval literals in expressions.

The string '2 hours' alone is not recognized as INTERVAL. The correct syntax is start_time + INTERVAL '2 hours'. Without INTERVAL keyword, it causes an error.