Data Types - PostgreSQL: Up and Running - page 95

[1,)
("2013-01-05 10:00:00","2013-08-13 14:00:00"]
A date range between 2013-01-05 and 2013-08-13 inclusive. Note the
canonicalization on the upper bound.
A date range greater than 2013-01-05 and less than or equal to 2013-08-13.
Notice the canonicalization.
An integer greater than 0 and less than or equal to infinity. Note the
canonicalization.
A timestamp greater than 2013-01-05 10:00 and less than or equal to 2013-08-13
14:00:00.
Datetimes in PostgreSQL can take on the values of -infinity and
infinity . For uniformity and in keeping with convention, we sug‐
gest that you always use [ for the former and ) for the latter where you
have a choice: tsrange and tstzrange .
Ranges can also be defined using constructor range functions, which go by the same
name as the range and can take two or three arguments. Here's an example:
SELECT daterange ( '2013-01-05' , 'infinity' , '[]' );
The third argument denotes the bound. If omitted, [) is the default. We suggest that
you always include the third element for clarity, because the default is not obvious.
Defining Tables with Ranges
Temporal ranges are popular. Suppose you have an employment table that stores em‐
ployment history. Instead of creating start and end dates, you can design a table as shown
in Example 5-20 . In the example, we add an index to the period column to speed up
queries using our range column.
Example 5-20. Table with date range
CREATE TABLE employment ( id serial PRIMARY KEY , employee varchar ( 20 ), period dater
ange );
CREATE INDEX idx_employment_period ON employment USING gist ( period );
INSERT INTO employment ( employee , period )
VALUES ( 'Alex' , '[2012-04-24, infinity)' :: daterange ), ( 'Sonia' , '[2011-04-24,
2012-06-01)' :: daterange ), ( 'Leo' , '[2012-06-20, 2013-04-20)' :: daterange ), ( 'Regi
na' , '[2012-06-20, 2013-04-20)' :: daterange );
Add a GiST index on the range field.