Interval type for durations in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the interval type used for in PostgreSQL?

The interval type stores a duration of time, like days, hours, minutes, or seconds, representing a span between two points in time.

beginner

How do you create an interval of 3 days in PostgreSQL?

You can write INTERVAL '3 days' to represent a duration of three days.

beginner

Can you add an interval to a timestamp in PostgreSQL? Give an example.

Yes. For example, SELECT timestamp '2024-06-01 10:00' + INTERVAL '2 hours'; adds 2 hours to the timestamp.

intermediate

What units can you use inside an interval literal in PostgreSQL?

You can use units like years, months, days, hours, minutes, seconds, and combinations like '1 year 2 months 3 days'.

advanced

How does PostgreSQL store intervals internally?

PostgreSQL stores intervals as three parts: months, days, and microseconds, allowing flexible representation of durations.

Which of these is a valid interval literal in PostgreSQL?

A INTERVAL '5 hours 30 minutes'

B INTERVAL 5 hours 30 minutes

C '5 hours 30 minutes' INTERVAL

D INTERVAL(5 hours 30 minutes)

The correct syntax is INTERVAL followed by a quoted string with the duration, like INTERVAL '5 hours 30 minutes'.

What does SELECT INTERVAL '1 day' + INTERVAL '2 hours'; return?

A An interval of 3 days

B An error because you cannot add intervals

C An interval of 1 day and 2 hours

D A timestamp value

Adding two intervals results in a combined interval representing the total duration.

How do you subtract 30 minutes from a timestamp in PostgreSQL?

A timestamp - INTERVAL '30 minutes'

B timestamp + INTERVAL '30 minutes'

C timestamp - '30 minutes'

D timestamp - 30

You subtract an interval literal from a timestamp to reduce the time by that duration.

Which part is NOT stored separately inside a PostgreSQL interval?

A Months

B Years

C Microseconds

D Days

Years are converted into months internally; PostgreSQL stores months, days, and microseconds separately.

What happens if you add an interval to a date in PostgreSQL?

A You get a timestamp with time zone

B You get an error

C The interval is ignored

D You get a new date shifted by the interval

Adding an interval to a date shifts the date by that duration, returning a new date.

Explain what the PostgreSQL interval type is and how you can use it with timestamps.

Think about how you tell time differences or add time in real life.

Describe the internal storage structure of PostgreSQL intervals and why it matters.

Consider how different units of time are handled inside the database.