postgresqltimerange

How to set the resolution of the TSTZRANGE Postgres type?


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 &&)
);

Solution

  • 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));