interval data type

From: "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: interval data type
Date: 2021-01-21 21:22:56
Message-ID: 0c229ba17fe7eb51223ebd8bb90cdfff.squirrel@webmail.harte-lyne.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)Harte-Lyne(dot)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Geoghegan 2021-01-21 21:29:33 Re: autovacuum verbose?
Previous Message Adrian Klaver 2021-01-21 21:00:52 Re: Error while running restore