Re: interval data type

> On Jan 21, 2021, at 13:22, James B. Byrne <byrnejb@harte-lyne.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
arrayof 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.
Forexample, 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;
thedocumentation 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
whereyou 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
thatare not a fixed number of seconds, such as months and years. 

--
-- Christophe Pettus
   xof@thebuild.com