Interval field in Postgresql

Question

What is the difference between these commands?

ALTER TABLE podcast_episodes ADD COLUMN duration interval;

ALTER TABLE podcast_episodes ADD COLUMN duration interval SECOND(0);

ALTER TABLE podcast_episodes ADD COLUMN duration interval SECOND;

I would like to store duration of a podcast episode and later have podcast_playback_history with first_mark and last_mark to calculate percentage of the whole episode listened.

The docs said

The interval type has an additional option, which is to restrict the set of stored fields by writing one of these phrases:

YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

YEAR TO MONTH

DAY TO HOUR

DAY TO MINUTE

DAY TO SECOND

HOUR TO MINUTE

HOUR TO SECOND

MINUTE TO SECOND

Note that if both fields and p are specified, the fields must include SECOND, since the precision applies only to the seconds.

Is there added benefit if I set it to second? Will it still be 16 bytes or will it be less? I tried using second and second(0), when I save the row it is still showing as '0 years 0 mons 0 days 0 hours 0 mins 2.00 secs'

Erwin Brandstetter · Accepted Answer · 2021-04-18T04:07:00.900

The added fields specification determines how strings with unmarked quantities are interpreted.

The manual:

When writing an interval constant with a fields specification, or when assigning a string to an interval column that was defined with a fields specification, the interpretation of unmarked quantities depends on the fields. For example INTERVAL '1' YEAR is read as 1 year, whereas INTERVAL '1' means 1 second.

I never use these modifiers for the type definition. Better stick to default intervals and avoid confusion. Always spell out quantity names in string literals (interval '13 seconds' or interval '13 sec'), never interval '13' - the only case where the added type modifier would kick in. I wouldn't want to depend on the table definition to interpret my input. Especially not for such an infrequently used feature.

The optional precision modifier (0) .. (6) only applies to seconds and is only allowed together with that field specification. The manual:

Note that if both fields and p are specified, the fields must include SECOND, since the precision applies only to the seconds.

It defines how many decimal digits are stored for sub-seconds. If you use it (which I would not), be aware that values are rounded, not truncated (like one might expect).

test=> SELECT '13.555555 sec'::interval(3);
   interval   
--------------
 00:00:13.556    -- !
(1 row)

test=> SELECT '13.555555 sec'::interval(0);
 interval 
----------
 00:00:14        -- !!!
(1 row)

Demo:

db<>fiddle here

Is there added benefit if I set it to second?

No, that's the default anyway. (And I would strongly suggest to stick with the default to avoid confusion.)

Will it still be 16 bytes or will it be less?

The size of an interval value is always 16 bytes.

Interval field in Postgresql

1 Answers1