Database Reference
In-Depth Information
tsrange
New in version 9.2; allows you to define opened and closed ranges of
timestamp
with no timezone
. The type consists of two timestamps and opened/closed range
qualifiers. For example,
'[2012-01-01 14:00, 2012-01-01 15:00)'::tsrange
defines a period starting at 14:00 but ending before 15:00. Refer to
Range Types
for
details.
tstzrange
New in version 9.2; allows you to define opened and closed ranges of
timestamp
with timezone
.
daterange
New in version 9.2; allows you to define opened and closed ranges of dates.
Time Zones: What They Are and Are Not
A common misconception with PostgreSQL time zone−aware data types is that Post‐
greSQL records an extra time marker with the datetime value itself. This is incorrect. If
you save
2012-2-14 18:08:00-8
(-8 being the Pacific offset from UTC), PostgreSQL
internally works like this:
1. Get the UTC time for 2012-02-14 18:08:00-8. This is 2012-02-15 04:08:00-0.
2. Store the value
2012-02-15 04:08:00
.
When you call the data back for display, PostgreSQL internally works like this:
1. Find the time zone observed by the server or the requested time zone (for instance,
America/New_York
).
2. Compute the offset for time zone for this UTC time. (-5 for
America/New_York
).
3. Determine the datetime with the offset (2012-02-15 16:08:00 with a -5 offset be‐
comes 2012-02-15 21:08:00).
4. Display the result (
2012-02-15 21:08:00-5
).
As you can see, PostgreSQL doesn't store the time zone but only uses it to convert the
datetime to UTC before storage. After that, the time zone information is gone. When
PostgreSQL displays the datetime, it always does so in the default time zone dictated by
the session, user, database, or server, in that order. If you use time-zone-aware data types,
we implore you to consider the consequence of a server move from one time zone to
another. Suppose you based a server in New York City and subsequently restored the
database in Los Angeles. All timestamps with time zone fields would suddenly display
in Pacific time. This is fine as long as you anticipate this behavior.
Here's an example of how something can go wrong. Suppose that McDonald's had its
server on the East Coast and the opening time for stores is
timetz
. A new McDonald's
