Alright, let’s talk about interval. If timestamp is the “when,” then interval is the “how long.” It’s the amount of time that has passed, a duration. It’s the difference between two timestamps, or the thing you add to a timestamp to get a new one. Simple, right? Well, mostly. But time is a fickle beast, and representing its passage gets messy because our calendars are a messy, human invention. PostgreSQL’s interval type is incredibly powerful, but with great power comes the responsibility to not accidentally add a million seconds to something.

The core idea is that an interval can be stored in a few ways. You can be vague and human about it (‘1 month’), you can be precise and computer-ish (‘3600 seconds’), or you can use a hybrid model. PostgreSQL handles all of it, but you need to know what you’re actually storing.

The Two Flavors of Interval: date-time and time-only

This is the first “gotcha.” An interval can represent a full duration that includes days, months, and years (a date-time interval), or it can represent a duration that’s less than a day (a time-only interval). The syntax differentiates them.

-- A 'date-time' interval. It has days, months, etc.
SELECT INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds';

-- A 'time-only' interval. It MUST include a colon.
SELECT INTERVAL '04:05:06'; -- 4 hours, 5 minutes, 6 seconds

Why does this matter? Context. If you write INTERVAL '1 day', it’s unambiguous. But if you write INTERVAL '25:00:00', is that 25 hours or is it a time-of-day that’s invalid? The colon syntax removes the ambiguity; it always means a duration of hours, minutes, and seconds. This becomes critical when doing arithmetic.

How Interval Arithmetic Actually Works (The Weird Parts)

This is where the designers made some… interesting choices. When you add an interval to a timestamp, the operation depends on the units in your interval. Days are treated as 24-hour periods. Hours, minutes, and seconds are treated as precise amounts of time. But months and years? They’re “calendar-aware.” This leads to brilliantly logical but initially confusing results.

-- Adding a precise duration: 24 hours is always 86,400 seconds.
SELECT TIMESTAMP '2023-03-12 14:00' + INTERVAL '24 hours';
-- Result: 2023-03-13 14:00:00

-- Adding a calendar-aware duration: "1 month" means "the same day next month."
SELECT TIMESTAMP '2023-01-31' + INTERVAL '1 month';
-- Result: 2023-02-28 (because February doesn't have 31 days)

See the potential for disaster? Adding 1 month is not the same as adding 30 days. The first operation is contextual and depends on the calendar. The second is absolute. This is the most common pitfall. If you’re calculating a 30-day free trial, for the love of all that is holy, use 30 days, not 1 month. Otherwise, someone who signs up on January 31st will have a very short February.

The Storage Internals and Input Parsing

Under the hood, an interval is stored as three separate integers: months, days, and microseconds. This is why it can handle these hybrid operations so efficiently. When you input something like INTERVAL '1 year 3 hours', PostgreSQL parses it and stores it as 12 months (for the year) and 10,800,000,000 microseconds (for the 3 hours).

The input parsing is also remarkably flexible, which is a blessing and a curse. All of these work:

SELECT INTERVAL '1 year 2 mons 3 days 04:05:06';
SELECT INTERVAL 'P1Y2M3DT4H5M6S'; -- ISO 8601 standard format (please use this)
SELECT INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds';

I cannot stress this enough: for anything non-trivial, use the ISO 8601 format (P...T...). It’s unambiguous, standardized, and sortable. The other formats rely on PostgreSQL’s sometimes-too-clever parser, and writing '1 mon' instead of '1 month' will work, but '1 min' is minutes, not months. It’s a recipe for a subtle, terrible bug. Clarity over cleverness.

Best Practices and Final Advice

  1. Explicit over Implicit: Always specify the units. Don’t rely on default interpretations. Be verbose.
  2. Know Your Units: Are you doing calendar math (project deadlines, billing cycles) or precise math (timeouts, session durations)? Choose month/year or day/hour/second accordingly. Mixing them is asking for trouble.
  3. Beware of Time Zones: If you add an interval to a timestamptz, the operation is performed in UTC and then converted back to your time zone. This usually does what you expect, but if your interval straddles a Daylight Saving Time shift, the local clock time might look funky. The duration in actual seconds will always be correct.
  4. Extracting Values: You can EXTRACT units from an interval, but remember it’s deconstructing the stored integers. An interval of 1 month will return 1 for the month field and 0 for the day field. It won’t convert that month into 30 days.

The interval type is a workhorse. Once you understand its split personality between calendar time and absolute time, you’ll stop fearing it and start using it to do genuinely powerful things. Just remember: it’s not a broken system, it’s a nuanced one. And nuance, in the world of time, is everything.