Date arithmetic with intervals in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to add 5 days to the current date.

PostgreSQL

SELECT CURRENT_DATE [1] INTERVAL '5 days';

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

A*

B+

C/

D-

Attempts:

3 left

💡 Hint

Common Mistakes

Using '-' instead of '+' causes subtraction.

Using '*' or '/' causes syntax errors.

In PostgreSQL, you add intervals to dates using the + operator.

2fill in blank

medium

Complete the code to subtract 2 months from the current date.

PostgreSQL

SELECT CURRENT_DATE [1] INTERVAL '2 months';

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

A+

B*

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' adds the interval instead of subtracting.

Using '*' or '/' causes errors.

Use the minus sign (-) to subtract intervals from dates in PostgreSQL.

3fill in blank

hard

Fix the error in the code to add 10 hours to the timestamp '2024-01-01 08:00:00'.

PostgreSQL

SELECT TIMESTAMP '2024-01-01 08:00:00' [1] INTERVAL '10 hours';

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

A+

B-

C*

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '-' subtracts the interval instead of adding.

Using '*' or '/' causes syntax errors.

The plus sign (+) correctly adds the interval to the timestamp.

4fill in blank

hard

Fill both blanks to subtract 15 days and then add 3 hours to the current timestamp.

PostgreSQL

SELECT CURRENT_TIMESTAMP [1] INTERVAL '15 days' [2] INTERVAL '3 hours';

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

A-

B+

C*

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' to subtract days causes wrong results.

Using '*' or '/' causes errors.

First subtract 15 days using '-', then add 3 hours using '+'.

5fill in blank

hard

Fill all three blanks to add 1 year, subtract 2 months, and add 10 days to the current date.

PostgreSQL

SELECT CURRENT_DATE [1] INTERVAL '1 year' [2] INTERVAL '2 months' [3] INTERVAL '10 days';

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

A+

B-

C*

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '-' to add intervals causes wrong results.

Using '*' or '/' causes syntax errors.

Add 1 year with '+', subtract 2 months with '-', then add 10 days with '+'.

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