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.

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