Postgresql DDL interval field ¶

Example ¶

           CREATE TABLE public.fiche_temps (
        id serial NOT NULL,
        temps_impute interval NOT NULL,
        etat int2 NOT NULL,
        created timestamptz NOT NULL,
        modified timestamptz NULL,
        id_employe int4 NOT NULL,
        id_projet int4 NOT NULL,
        commentaire text NOT NULL,
        CONSTRAINT fiche_temps_etat_check CHECK ((etat >= 0)),
        CONSTRAINT fiche_temps_pkey PRIMARY KEY (id),
        CONSTRAINT unique_employe_projet_created UNIQUE (id_employe, id_projet, created)
);
CREATE INDEX employe_projet_created_index ON public.fiche_temps USING btree (id_employe, id_projet, created);
CREATE INDEX fiche_temps_id_employe_cee33221 ON public.fiche_temps USING btree (id_employe);
CREATE INDEX fiche_temps_id_projet_971aea17 ON public.fiche_temps USING btree (id_projet);

          

Introduction to PostgreSQL interval data type ¶

The interval data type allows you to store and manipulate a period of time in years, months, days, hours, minutes, seconds, etc.

The following illustrates the interval type:

           @ interval [ fields ] [ (p) ]

          

An interval value requires 16 bytes storage size that can store a period with the allowed range is from -178,000,000 years to 178,000,000 years.

In addition, an interval value can have an optional precision value p with the permitted range is from 0 to 6. The precision p is the number of fraction digits retained in the second fields.

The at sign ( @) is optional therefore you can omit it.

The following examples show some interval values:

           interval '2 months ago';
interval '3 hours 20 minutes';

Internally, PostgreSQL stores interval values as months, days, and seconds.

The months and days values are integers while the seconds can field can have fractions.