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.