|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.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||4713 BC → 5874897 AD|| 1 microsecond / 14 digits
||8 bytes||both date and time, with time zone||4713 BC → 5874897 AD|| 1 microsecond / 14 digits
||12 bytes||time intervals||-178000000 years → 178000000 years|| 1 microsecond / 14 digits
||4 bytes||dates only||4713 BC → 5874897 AD|| 1 day
||8 bytes||times of day only||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|
Note: Prior to PostgreSQL 7.3, writing just
timestampwas equivalent to
timestamp with time zone. This was changed for SQL compliance.
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 double precision floating-point numbers (currently the default), the effective limit of precision may be less than 6.
timestampvalues are stored as seconds before or after midnight 2000-01-01. Microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. When
timestampvalues are stored as eight-byte integers (a compile-time option), microsecond precision is available over the full range of values. However eight-byte integer timestamps have a more limited range of dates than shown above: from 4713 BC up to 294276 AD. The same compile-time option also determines whether
intervalvalues are stored as floating-point 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.
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 new
applications and are encouraged to move any old
ones over when appropriate. Any or all of these internal types
might disappear in a future release.
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|