Date arithmetic with intervals in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an interval in PostgreSQL?

An interval in PostgreSQL represents a span of time, such as days, hours, or minutes, that can be added to or subtracted from date or timestamp values.

beginner

How do you add 3 days to the current date in PostgreSQL?

You can add 3 days using: SELECT CURRENT_DATE + INTERVAL '3 days';

beginner

What happens if you subtract an interval from a timestamp?

Subtracting an interval from a timestamp moves the timestamp backward by that time span. For example, timestamp - INTERVAL '1 hour' gives the time one hour earlier.

intermediate

Can intervals include multiple units like days and hours together?

Yes, intervals can combine units, e.g., INTERVAL '2 days 4 hours' adds 2 days and 4 hours together.

intermediate

How do you find the difference between two dates as an interval?

Subtract one date from another, e.g., SELECT '2024-06-10'::date - '2024-06-01'::date; returns the number of days as an integer. For timestamps, subtraction returns an interval.

Which SQL expression adds 5 hours to the current timestamp?

A SELECT CURRENT_TIMESTAMP + INTERVAL '5 hours';

B SELECT CURRENT_TIMESTAMP + 5;

C SELECT CURRENT_TIMESTAMP + '5 hours';

D SELECT CURRENT_TIMESTAMP + INTERVAL 5;

Only option A correctly uses INTERVAL with a quoted string specifying '5 hours'.

What is the result type when subtracting two timestamps in PostgreSQL?

A Integer

B Interval

C Date

D Text

Subtracting two timestamps returns an interval representing the time difference.

How do you represent an interval of 1 month and 10 days?

A INTERVAL '1 month 10 days'

B INTERVAL '10 days 1 month'

C INTERVAL '1 day 10 months'

D INTERVAL '10 months 1 day'

The correct syntax is to list units in order, e.g., '1 month 10 days'.

Which of these adds 30 minutes to a timestamp?

A timestamp + INTERVAL '30 minutes'

B timestamp + INTERVAL 30

C timestamp + '30 minutes'

D timestamp + INTERVAL '30'

Only option A correctly uses INTERVAL with a quoted string specifying the unit.

What does this query return? SELECT CURRENT_DATE - INTERVAL '1 day';

A Yesterday's date

B Tomorrow's date

C Current date plus one day

D Error

Subtracting 1 day interval from current date returns yesterday's date.

Explain how to add and subtract intervals from dates or timestamps in PostgreSQL.

Think about how you can move forward or backward in time using intervals.

Describe how to find the difference between two timestamps and what type of result you get.

Consider what happens when you subtract one time from another.

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