|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
6.5 Date/Time Types
|Name||Storage Size||Description||Low Value → High Value|| Resolution
||8 bytes||both date and time (no time zone)||4713 BC → 294276 AD|| 1 microsecond / 14 digits
||8 bytes||both date and time, with time zone||4713 BC → 294276 AD|| 1 microsecond / 14 digits
||4 bytes||date (no time of day)||4713 BC → 5874897 AD|| 1 day
||8 bytes||time of day (no date)||00:00:00 → 24:00:00|| 1 microsecond / 14 digits
||12 bytes||times of day only, with time zone||00:00:00+1459 → 24:00:00-1459|| 1 microsecond / 14 digits
||12 bytes||time interval||-178000000 years → 178000000 years||1 microsecond / 14 digits|
Note: The SQL standard requires that writing just
timestampbe equivalent to
timestamp without time zone, and PostgreSQL honors that behavior. (Releases prior to 7.3 treated it as
timestamp with time zone.)
interval accept an optional precision value
p which specifies the number of
fractional digits retained in the seconds field. By default, there
is no explicit bound on precision. The allowed range of
p is from 0 to 6 for the
timestampvalues are stored as eight-byte integers (currently the default), microsecond precision is available over the full range of values. When
timestampvalues are stored as double precision floating-point numbers instead (a deprecated compile-time option), the effective limit of precision might be less than 6.
timestampvalues are stored as seconds before or after midnight 2000-01-01. When
timestampvalues are implemented using floating-point numbers, microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. Note that using floating-point datetimes allows a larger range of
timestampvalues to be represented than shown above: from 4713 BC up to 5874897 AD.
The same compile-time option also determines whether
intervalvalues are stored as floating-point numbers or eight-byte integers. In the floating-point case, large
intervalvalues degrade in precision as the size of the interval increases.
time types, the allowed range of
p is from 0 to 6 when eight-byte integer
storage is used, or from 0 to 10 when floating-point storage is used.
interval type has an additional option, which is
to restrict the set of stored fields by writing one of these phrases:
YEAR MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE HOUR TO SECOND MINUTE TO SECOND
Note that if both fields and
p are specified, the
fields must include
since the precision applies only to the seconds.
time with time zone is defined by the SQL
standard, but the definition exhibits properties which lead to
questionable usefulness. In most cases, a combination of
timestamp without time
timestamp with time zone should
provide a complete range of date/time functionality required by
reltime are lower precision types which are used internally.
You are discouraged from using these types in
applications; these internal types
might disappear in a future release.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|