Interval type for durations in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to declare a column that stores time durations using the interval type.

PostgreSQL

CREATE TABLE events (duration [1]);

Drag options to blanks, or click blank then click option'

AINTERVAL

BVARCHAR(10)

CINTEGER

DDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using DATE or TIMESTAMP instead of INTERVAL for durations.

Using INTEGER which stores numbers but not time intervals.

The INTERVAL type in PostgreSQL is used to store durations or time intervals.

2fill in blank

medium

Complete the code to add 3 hours to the current timestamp using interval.

PostgreSQL

SELECT NOW() + [1] '3 hours';

Drag options to blanks, or click blank then click option'

ATIMESTAMP

BTIME

CINTERVAL

DDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using DATE or TIME which are not durations.

Forgetting to specify INTERVAL before the string.

Adding an INTERVAL value to a timestamp adds the specified duration.

3fill in blank

hard

Fix the error in the query that tries to subtract 30 minutes from a timestamp.

PostgreSQL

SELECT CURRENT_TIMESTAMP - [1] '30 minutes';

Drag options to blanks, or click blank then click option'

AINTERVAL

BTIME

CTIMESTAMP

DDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TIMESTAMP or DATE which represent points in time, not durations.

Omitting the INTERVAL keyword.

To subtract a duration, you must use an INTERVAL type.

4fill in blank

hard

Fill both blanks to create an interval of 2 days and 5 hours.

PostgreSQL

SELECT [2] '[1]';

Drag options to blanks, or click blank then click option'

A2 days 5 hours

B'2 days 5 hours'

CINTERVAL

DDAYS

Attempts:

3 left

💡 Hint

Common Mistakes

Putting quotes inside the string literal again.

Using DAYS instead of INTERVAL keyword.

The INTERVAL keyword is used with a string describing the duration, like '2 days 5 hours'.

5fill in blank

hard

Fill all three blanks to calculate the total duration in minutes from 1 hour, 30 minutes, and 45 seconds.

PostgreSQL

SELECT EXTRACT(EPOCH FROM ([1] + [2] + [3])) / 60 AS total_minutes;

Drag options to blanks, or click blank then click option'

AINTERVAL '1 hour'

BINTERVAL '30 minutes'

CINTERVAL '45 seconds'

DINTERVAL '1 day'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong interval values or missing INTERVAL keyword.

Not dividing by 60 to convert seconds to minutes.

Adding intervals and extracting epoch (seconds) then dividing by 60 gives total minutes.

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