pg_attribute.atttypmod for interval type
Hi,
I work on a driver for Postgres that utilizes the extended query protocol
and have a question about the row description message for interval types.
I am trying to use the type modifier value from the message to determine
the precision of the interval. This is something I can do for timestamp
types because the type modifier in the message is equal to the precision
(or -1 if not specified).
For interval types it seems like the type modifier is related to the
precision but the values are not equal. Using Postgres 13.10 I see the
following values for pg_attribute.attttypmod when creating columns of type
interval(1), interval(2), ..., interval(6)
2147418113
2147418114
2147418115
2147418116
2147418117
2147418118
I can see the value goes up by 1 each time the precision is increased, but
I'm not sure how to interpret the fact that it starts at 2147418113
instead of 1.
My question is: how are the values meant to be interpreted for interval
types? Thanks very much for your help.
Greg Rychlewski <greg.rychlewski@gmail.com> writes:
My question is: how are the values meant to be interpreted for interval
types? Thanks very much for your help.
Interval typmods include a fractional-seconds-precision field as well
as a bitmask indicating the allowed interval fields (per the SQL
standard's weird syntax such as INTERVAL DAY TO SECOND). Looking at
the source code for intervaltypmodout() might be helpful:
The referenced macros are mostly in utils/timestamp.h and
utils/datetime.h.
regards, tom lane
On 07/27/24 00:32, Tom Lane wrote:
Interval typmods include a fractional-seconds-precision field as well
as a bitmask indicating the allowed interval fields (per the SQL
standard's weird syntax such as INTERVAL DAY TO SECOND). Looking at
the source code for intervaltypmodout() might be helpful:
Also, for this kind of question, an overview of a type modifier's
contents can be found in the javadoc for the WIP PL/Java 1.7, which is
intended to model such things accurately.[0]https://tada.github.io/pljava/preview1.7/pljava-api/apidocs/org.postgresql.pljava/org/postgresql/pljava/adt/Timespan.Interval.Modifier.html
The model is aimed at the logical level, that is, to represent
what information is present in the typmod, the precise semantics, what
combinations are allowed/disallowed, and so on, but not the way PostgreSQL
physically packs the bits. So, for this case, what you would find there
is essentially what Tom already said, about what's logically present; it
doesn't save you the effort of looking in the PostgreSQL source if you want
to independently implement unpacking the bits.
For possible future typmod questions, it may serve as a quick way to
get that kind of logical-level description at moments when Tom is away
from the keyboard.
Regards,
-Chap
[0]: https://tada.github.io/pljava/preview1.7/pljava-api/apidocs/org.postgresql.pljava/org/postgresql/pljava/adt/Timespan.Interval.Modifier.html
https://tada.github.io/pljava/preview1.7/pljava-api/apidocs/org.postgresql.pljava/org/postgresql/pljava/adt/Timespan.Interval.Modifier.html
I just noticed a nit in that javadoc: it says the field combination
must be "one of the named constants in this interface" but you don't
find them in the Interval.Modifier interface; they're in the containing
interface Interval itself.