- publishing free software manuals
PostgreSQL Reference Manual - Volume 1 - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
ISBN 0954612027
RRP £32.00 ($49.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

6.5 Date/Time Types

PostgreSQL supports the full set of SQL date and time types, shown in Table 6-9. The operations available on these data types are described in section 7.9 Date/Time Functions and Operators.

Table 6-9: Date/Time Types
Name Storage Size Description Low ValueHigh Value Resolution
timestamp [ (p) ] [ without time zone ] 8 bytes both date and time 4713 BC → 5874897 AD 1 microsecond / 14 digits
timestamp [ (p) ] with time zone 8 bytes both date and time, with time zone 4713 BC → 5874897 AD 1 microsecond / 14 digits
interval [ (p) ] 12 bytes time intervals -178000000 years → 178000000 years 1 microsecond / 14 digits
date 4 bytes dates only 4713 BC → 5874897 AD 1 day
time [ (p) ] [ without time zone ] 8 bytes times of day only 00:00:00 → 24:00:00 1 microsecond / 14 digits
time [ (p) ] with time zone 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 timestamp was equivalent to timestamp with time zone. This was changed for SQL compliance.

time, timestamp, and 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 timestamp and interval types.

Note: When timestamp values are stored as double precision floating-point numbers (currently the default), the effective limit of precision may be less than 6. timestamp values 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 timestamp values 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 time and interval values are stored as floating-point or eight-byte integers. In the floating-point case, large interval values degrade in precision as the size of the interval increases.

For the 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.

The type 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 date, time, timestamp without time zone, and timestamp with time zone should provide a complete range of date/time functionality required by any application.

The types abstime and 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 0954612027PostgreSQL Reference Manual - Volume 1 - SQL Language ReferenceSee the print edition