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.

Practice

1. What does the following PostgreSQL expression do?
SELECT CURRENT_DATE + INTERVAL '5 days';

easy

A. Returns the current date without change

B. Subtracts 5 days from the current date

C. Causes a syntax error

D. Adds 5 days to the current date

Solution

1. Step 1: Understand the CURRENT_DATE keyword

   CURRENT_DATE returns today's date without time.

2. Step 2: Add interval of 5 days

   Adding INTERVAL '5 days' increases the date by 5 days.

3. Final Answer:

   Adds 5 days to the current date -> Option D

4. Quick Check:

   Adding interval '5 days' = date + 5 days [OK]

Hint: Adding INTERVAL 'X days' shifts date forward by X days [OK]

Common Mistakes:

• Confusing addition with subtraction
• Thinking CURRENT_DATE includes time
• Using wrong interval syntax

2. Which of the following is the correct syntax to subtract 3 months from a date column named order_date?

easy

A. order_date - '3 months'

B. order_date - INTERVAL '3 months'

C. order_date - INTERVAL 3 months

D. order_date - INTERVAL '3 month'

Solution

1. Step 1: Identify correct interval syntax

   INTERVAL must be followed by a quoted string with number and unit, e.g., '3 months'.

2. Step 2: Check subtraction from date column

   Subtracting INTERVAL '3 months' from order_date is valid syntax.

3. Final Answer:

   order_date - INTERVAL '3 months' -> Option B

4. Quick Check:

   Subtract INTERVAL '3 months' with quotes [OK]

Hint: Always quote interval value like '3 months' [OK]

Common Mistakes:

• Omitting quotes around interval string
• Using singular 'month' instead of plural 'months'
• Trying to subtract string without INTERVAL keyword

3. What is the result of this query if today is 2024-06-15?

SELECT (DATE '2024-06-15' + INTERVAL '1 month' - INTERVAL '10 days')::date AS new_date;

medium

A. 2024-07-25

B. 2024-05-25

C. 2024-07-05

D. 2024-06-05

Solution

1. Step 1: Add 1 month to 2024-06-15

   Adding 1 month results in 2024-07-15.

2. Step 2: Subtract 10 days from 2024-07-15

   Subtracting 10 days gives 2024-07-05.

3. Final Answer:

   2024-07-05 -> Option C

4. Quick Check:

   2024-06-15 + 1 month - 10 days = 2024-07-05 [OK]

Hint: Add then subtract intervals stepwise for correct date [OK]

Common Mistakes:

• Subtracting before adding
• Confusing day and month units
• Not casting to date and including time

4. Identify the error in this query:

SELECT CURRENT_DATE + INTERVAL 5 days;

medium

A. Missing quotes around interval value

B. CURRENT_DATE cannot be used with intervals

C. INTERVAL keyword is misspelled

D. Adding intervals to dates is not allowed

Solution

1. Step 1: Check INTERVAL syntax

   INTERVAL requires the value to be a quoted string, e.g., '5 days'.

2. Step 2: Identify missing quotes error

   The query lacks quotes around 5 days, causing syntax error.

3. Final Answer:

   Missing quotes around interval value -> Option A

4. Quick Check:

   INTERVAL '5 days' needs quotes [OK]

Hint: Always put interval value in single quotes [OK]

Common Mistakes:

• Writing INTERVAL 5 days without quotes
• Assuming CURRENT_DATE can't be used with intervals
• Confusing INTERVAL keyword spelling

5. You want to find all orders from a table orders where the order_date is within the last 3 months from today. Which query correctly uses date arithmetic with intervals?

hard

A. SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '3 months';

B. SELECT * FROM orders WHERE order_date <= CURRENT_DATE + INTERVAL '3 months';

C. SELECT * FROM orders WHERE order_date > CURRENT_DATE + INTERVAL '3 months';

D. SELECT * FROM orders WHERE order_date < CURRENT_DATE - INTERVAL '3 months';

Solution

1. Step 1: Understand the requirement

   We want orders from the last 3 months, so order_date should be after (or equal to) 3 months ago.

2. Step 2: Use CURRENT_DATE minus 3 months

   Subtracting INTERVAL '3 months' from CURRENT_DATE gives the date 3 months ago.

3. Step 3: Write condition for order_date

   order_date >= CURRENT_DATE - INTERVAL '3 months' selects orders within last 3 months.

4. Final Answer:

   SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '3 months'; -> Option A

5. Quick Check:

   Filter dates after CURRENT_DATE - 3 months [OK]

Hint: Use >= CURRENT_DATE - INTERVAL 'X months' for recent dates [OK]

Common Mistakes:

• Using + INTERVAL instead of - INTERVAL
• Using < instead of >= for filtering recent dates
• Confusing direction of date comparison