Thread: interval data type

What is the difference between interval(3)[] and simply interval(3)?  Where in
the documentation is the [] syntax discussed?

This question arises only because I am trying to map an Informix database
column defined as lead_time     INTERVAL DAY(3) TO DAY      to an equivalent
PostgreSQL datatype.

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?


As a side note:

The use of of the word 'fields' in the manual discussion regarding interval is,
to me, misleading in that only one 'field' value is permitted.

    "The interval type has an additional option, which is to restrict the set
of stored fields by writing one of these phrases:
. . .
    Note that if both fields and p are specified, the fields must include
SECOND, since the precision applies only to the seconds."

The manual says 'one of these phrases'; but then uses the word "fields"
together with the word "includes" and thereby implies that multiple selections
are permitted. Given the brevity of the exposition this initially confused me. 
Are there other types of 'fields' that may be used with interval that are not
given?

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

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

If someone would be kind enough to explain these things to me I would be
appreciative.

Regards,



-- 
***          e-Mail is NOT a SECURE channel          ***
        Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3

James B. Byrne schrieb am 21.01.2021 um 22:22:
> What is the difference between interval(3)[] and simply interval(3)?  Where in
> the documentation is the [] syntax discussed?

The [] denotes an array of intervals.

So in a column defined as interval[] you can store multiple intervals, just
like in an array of text (text[]), where you can store multiple strings.

This is discussed in the chapter about arrays:

    https://www.postgresql.org/docs/current/arrays.html

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

What would that integer represent? Seconds? Minutes? Hours? Fractional days?

Using an interval makes things a lot easier, because you can store any "duration"
in it without the need to know what the unit is that is stored in the column.

Also timestamp values and intervals go well together, but not timestamps and integers

You can add an interval to a timestamp and the result is a new timestamp.
If you stored the "duration" as integer, you would first need to convert the
integer to an interval.

> 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