Date arithmetic with intervals in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate date after adding interval

What is the result of this query?

SELECT DATE '2024-01-15' + INTERVAL '10 days';

PostgreSQL

SELECT DATE '2024-01-15' + INTERVAL '10 days';

A 2024-01-15 10:00:00

B 2024-01-25

C 2024-01-05 00:00:00

D 2024-01-25 00:00:00

💡 Hint

Adding an interval to a date returns a timestamp with time set to midnight.

Adding an interval to a DATE type in PostgreSQL returns a TIMESTAMP. The date '2024-01-15' plus 10 days is '2024-01-25 00:00:00'.

❓ query_result

intermediate

Subtract interval from timestamp

What is the output of this query?

SELECT TIMESTAMP '2024-06-01 15:30:00' - INTERVAL '3 hours 15 minutes';

PostgreSQL

SELECT TIMESTAMP '2024-06-01 15:30:00' - INTERVAL '3 hours 15 minutes';

A 2024-06-01 18:45:00

B 2024-06-01 12:15:00

C 2024-06-01 15:15:00

D 2024-06-01 03:15:00

💡 Hint

Subtracting an interval reduces the timestamp by that amount.

Subtracting 3 hours and 15 minutes from 15:30 results in 12:15 on the same day.

📝 Syntax

advanced

Identify the syntax error in interval addition

Which option will cause a syntax error when adding an interval to a date in PostgreSQL?

A SELECT DATE '2024-03-10' + '1 month';

B SELECT DATE '2024-03-10' + INTERVAL '1 month';

C SELECT DATE '2024-03-10' + INTERVAL '30 days';

D SELECT DATE '2024-03-10' + INTERVAL '1 year';

💡 Hint

Intervals must be explicitly cast or written with INTERVAL keyword.

Option A tries to add a string directly to a date without casting it as an interval, causing a syntax error.

❓ optimization

advanced

Optimize interval addition for multiple rows

Given a table events(event_date DATE), which query is more efficient to add 7 days to all event_date values?

A SELECT event_date + INTERVAL '7 days' FROM events;

B SELECT event_date + INTERVAL '168 hours' FROM events;

C SELECT event_date + INTERVAL '1 week' FROM events;

D SELECT event_date + INTERVAL '7 days 0 hours' FROM events;

💡 Hint

Using standard interval units can help the query planner optimize better.

INTERVAL '1 week' is a standard interval unit and is more readable and potentially optimized than specifying days or hours explicitly.

🧠 Conceptual

expert

Understanding interval multiplication and addition

What is the output of this query?

SELECT DATE '2024-01-01' + INTERVAL '1 day' * 3;

PostgreSQL

SELECT DATE '2024-01-01' + INTERVAL '1 day' * 3;

A 2024-01-04 00:00:00

B 2024-01-03 00:00:00

C 2024-01-01 03:00:00

D Error: cannot multiply interval by integer

💡 Hint

Multiplying an interval by an integer repeats the interval that many times.

INTERVAL '1 day' * 3 equals 3 days. Adding 3 days to 2024-01-01 results in 2024-01-04 00:00:00. PostgreSQL allows multiplying intervals by integers, so the expression is valid and returns 2024-01-04 00:00:00.