Postgres supports range types(permalink) such as int4range
or tstzrange
. The documentation for tstzrange
is "Range of timestamp with time zone
".
The default precision for timestamps is to use 6 digits for second fractions (microsecond resolution), however my application uses 3 digits for its timestamps (millisecond) through timestamp (3) with time zone
. For consistency I would like to also use millisecond-resolution for my time periods.
What is the default tstzrange
resolution? How can I configure it?
I tried to define my table using tstzrange(3)
but this form is not supported. Applied to the example from the documentation, it would look like:
CREATE TABLE reservation (
during tstzrange(3),
EXCLUDE USING GIST (during WITH &&)
);
As of Postgres 13, the time resolution for tstzrange
uses 6 digit for second fractions (microsecond resolution). This resolution cannot be changed.
To create a range between timestamps with a custom resolution, you have to define a new range type. Here is the query to create a timestamp range with a millisecond resolution:
CREATE TYPE tstzrange3 AS RANGE (
subtype = timestamp (3) with time zone
);
This defines both the tstzrange3
type as well as a constructor function with the same name.
You can use this type like any other range:
CREATE TABLE reservation (
during tstzrange3,
EXCLUDE USING GIST (during WITH &&)
);
INSERT INTO reservation(during) VALUES(tstzrange3(NOW(), NULL));