Data Types - PostgreSQL: Up and Running - page 88

In Example 5-9 , we are getting back a timestamp without time zone. So the answer you
get when you run this same query will be the same as mine, regardless of where in the
world you are.
Example 5-9. Timestamp with time zone to timestamp at location
SELECT '2012-02-28 10:00 PM America/Los_Angeles' :: timestamptz AT TIME ZONE 'Europe/
Paris' ;
2012-02-29 07:00:00
The query is asking: what time is it in Paris if it's 2012-02-28 10:00 p.m. in Los Angeles?
Note the absence of UTC offset in the result. Also, notice how you can specify a time
zone with its official name rather than just an offset. Visit Wikipedia for a list of official
time zone names .
Datetime Operators and Functions
The inclusion of a temporal interval data type greatly eases date and time arithmetic in
PostgreSQL. Without it, we'd have to create another family of functions or use a nesting
of functions as many other databases do. With intervals, we can add and subtract time‐
stamp data simply by using the arithmetic operators we're intimately familiar with. The
following examples demonstrate operators and functions used with date and time data
types.
The + adds an interval to a temporal type:
SELECT '2012-02-10 11:00 PM' :: timestamp + interval '1 hour' ;
2012-02-11 00:00:00
You can also add intervals:
SELECT '23 hours 20 minutes' :: interval + '1 hour' :: interval ;
24:20:00
The - operator subtracts an interval from a temporal type:
SELECT '2012-02-10 11:00 PM' :: timestamptz - interval '1 hour' ;
2012-02-10 22:00:00-05
OVERLAPS , demonstrated in Example 5-10 , returns true if two temporal ranges overlap.
This is an ANSI SQL operator equivalent to the overlaps function. OVERLAPS takes four
parameters, the first pair constituting one range and the last pair constituting the other
range. An overlap considers the time periods to be half open, meaning that the start
time is included but the end time is outside the range. This is slightly different behavior
from the common BETWEEN operator, which considers both start and end to be included.
This quirk won't make a difference unless one of your ranges is a fixed point in time (a