Re: interval data type

> On Jan 21, 2021, at 13:22, James B. Byrne <byrnejb(at)harte-lyne(dot)ca> wrote:
>
> What is the difference between interval(3)[] and simply interval(3)? Where in
> the documentation is the [] syntax discussed?

The [] syntax means an array. For example, float[] means an array of floating point numbers, so interval[] means an array of intervals.

> I got this to work with: ADD COLUMN lead_time interval day; and also with:
> ADD COLUMN lead_time interval(3); but I do not understand what these mean
> frankly. Does the form 'interval(3) imply a field value of SECOND?

No. An interval in PostgreSQL has multiple components: the year, month, and day intervals are all stored separately. For example, if months were always converted to seconds (or days), this wouldn't work properly:

xof=# SELECT '2021-01-01'::date + '1 month'::interval;
?column?
---------------------
2021-02-01 00:00:00
(1 row)

xof=# SELECT '2021-02-01'::date + '1 month'::interval;
?column?
---------------------
2021-03-01 00:00:00
(1 row)

The value in parenthesis is the number of decimal places to store fractional seconds:

xof=# select '0.33312312'::interval;
interval
-----------------
00:00:00.333123
(1 row)

xof=# select '0.33312312'::interval(3);
interval
--------------
00:00:00.333
(1 row)

> Are there other types of 'fields' that may be used with interval that are not
> given?

No, that list is exhaustive. The "fields" in the discussion of interval are not the same as the columns in a table; the documentation is talking about the components of an interval.

> I could not find a definition of 'sectored fields' in the manual. What is its
> meaning?

I don't believe that's a thing in PostgreSQL, and I didn't see the word "sectored" in the documentation. Can you quote where you saw it?

> Also I do not understand under what circumstance one would use the interval
> type in place of a simple integer.

Interval represents more than just a count of seconds or milliseconds, or some other unit; it also includes intervals that are not a fixed number of seconds, such as months and years.

--
-- Christophe Pettus
xof(at)thebuild(dot)com