Database Reference
In-Depth Information
ative infinity, relieving us from having to create conventions that we'll surely forget.
Finally, version 9.2 unveiled
range types
that provide support for temporal ranges with
a whole slew of companion operators, functions, and indexes. We cover range types in
“Range Types” on page 93
.
At last count, PostgreSQL has nine temporal data types. Understanding their distinc‐
tions is important to ensuring that you choose the right data type for the job. All of the
types except
range
abide by ANSI SQL standards. Other leading database products
support some, but not all, of these data types. Oracle has the most varieties of temporal
types; SQL Server ranks second; and MySQL comes in last, with no support for time
zones in any version.
Because PostgreSQL temporal types could be unique, we'll describe each in finer detail:
date
Just stores the month, day, and year, with no time zone awareness and no concept
of hours, minutes, or seconds.
time
(aka
time without time zone
)
Records hours, minutes, and seconds with no awareness of time zone or calendar
dates.
timestamp
(aka
timestamp without time zone
)
Records both calendar dates and time (hours, minutes, seconds) but does not care
about the time zone. As such, the displayed value of this data won't change when
you change your server's time zone.
timestamptz
(aka
timestamp with time zone
)
A time zone−aware date and time data type. Internally,
timestamptz
is stored in
Coordinated Universal Time (UTC), but its display defaults to the time zone of the
server (or database/user/session if you observe differing time zones at those levels).
If you input a timestamp with no time zone and cast it to one with the time zone,
PostgreSQL assumes the server's time zone. If you change your server's time zone,
you'll see all the displayed times change.
timetz
(aka
time with time zone
)
The lesser-used sister of
timestamptz
. It is time zone−aware but does not store the
date. It always assumes DST of the current time. For some programming languages
with no concept of time without date, it might map
timetz
to a timestamp with a
time zone at the beginning of time (for example, Unix Epoch 1970, thus resulting
in DST of year 1970 being used).
interval
A duration of time in hours, days, months, minutes, and others. It comes in handy
for doing date-time arithmetic. For example, if the world is supposed to end in
exactly 666 days from now, all you have to do is add an interval of 666 days to the
current time to get the exact moment when it will happen (and plan accordingly).
