From 1def28ff20c1f468eb3aa7c3ee3777d975bfc3fb Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Tue, 4 May 1999 02:22:13 +0000 Subject: [PATCH] Include specific info on available timezones. Document date/time input parsing procedure. --- doc/src/sgml/datatype.sgml | 509 ++++++++++++++++++++++++--------------------- 1 file changed, 273 insertions(+), 236 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 4c45cb01af..245fd1d4a0 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -516,214 +516,226 @@ This is set at compile time and may change in a future release. - -Date/Time Types - - -There are two fundamental kinds of date and time measurements: - absolute clock times and relative time intervals. -Both kinds of quantities should have behaviors demonstrating both -continuity and smoothness. -Postgres supplies two primary user-oriented -date and time types, -datetime and timespan, as well as -the related SQL92 types timestamp, -interval, -date and time. - - - -In a future release, datetime and timespan are likely -to merge with the SQL92 types timestamp, -interval. -Other date and time types are also available, mostly -for historical reasons. - - - - -<productname>Postgres</productname> Date/Time Types -Date/Time - - - - Date/Time Type - Storage - Recommendation - Description - - - - - abstime - 4 bytes - original date and time - limited range - - - date - 4 bytes - SQL92 type - wide range - - - datetime - 8 bytes - best general date and time - wide range, high precision - - - interval - 12 bytes - SQL92 type - equivalent to timespan - - - reltime - 4 bytes - original time interval - limited range, low precision - - - time - 4 bytes - SQL92 type - wide range - - - timespan - 12 bytes - best general time interval - wide range, high precision - - - timestamp - 4 bytes - SQL92 type - limited range - - - -
- -timestamp is currently implemented separately from -datetime, although they share input and output routines. - + + Date/Time Types + + + There are two fundamental kinds of date and time measurements + provided by Postgres: + absolute clock times and relative time intervals. + Both kinds of time measurements should demonstrate both + continuity and smoothness. + - - -<productname>Postgres</productname> Date/Time Ranges -Ranges - - - - Date/Time Type - Earliest - Latest - Resolution - - - - - abstime - 1901-12-14 - 2038-01-19 - 1 sec - - - date - 4713 BC - 32767 AD - 1 day - - - datetime - 4713 BC - 1465001 AD - 1 microsec to 14 digits - - - interval - -178000000 years - 178000000 years - 1 microsec - - - reltime - -68 years - +68 years - 1 sec - - - time - 00:00:00.00 - 23:59:59.99 - 1 microsec - - - timespan - -178000000 years - 178000000 years - 1 microsec (14 digits) - - - timestamp - 1901-12-14 - 2038-01-19 - 1 sec - - - -
- + + Postgres supplies two primary user-oriented + date and time types, + datetime and timespan, as well as + the related SQL92 types timestamp, + interval, + date and time. + - -Postgres endeavors to be compatible with -SQL92 definitions for typical usage. -The SQL92 standard has an odd mix of date and -time types and capabilities. Two obvious problems are: + + In a future release, datetime and timespan are likely + to merge with the SQL92 types timestamp, + interval. + Other date and time types are also available, mostly + for historical reasons. + - - - -Although the date type -does not have an associated time zone, the -time type can or does. + + + <productname>Postgres</productname> Date/Time Types + Date/Time + + + + Date/Time Type + Storage + Recommendation + Description + + + + + abstime + 4 bytes + original date and time + limited range + + + date + 4 bytes + SQL92 type + wide range + + + datetime + 8 bytes + best general date and time + wide range, high precision + + + interval + 12 bytes + SQL92 type + equivalent to timespan + + + reltime + 4 bytes + original time interval + limited range, low precision + + + time + 4 bytes + SQL92 type + wide range + + + timespan + 12 bytes + best general time interval + wide range, high precision + + + timestamp + 4 bytes + SQL92 type + limited range + + + +
+ + timestamp is currently implemented separately from + datetime, although they share input and output routines. + - - -The default time zone is specified as a constant integer offset -from GMT/UTC. + + + <productname>Postgres</productname> Date/Time Ranges + Ranges + + + + Date/Time Type + Earliest + Latest + Resolution + + + + + abstime + 1901-12-14 + 2038-01-19 + 1 sec + + + date + 4713 BC + 32767 AD + 1 day + + + datetime + 4713 BC + 1465001 AD + 1 microsec to 14 digits + + + interval + -178000000 years + 178000000 years + 1 microsec + + + reltime + -68 years + +68 years + 1 sec + + + time + 00:00:00.00 + 23:59:59.99 + 1 microsec + + + timespan + -178000000 years + 178000000 years + 1 microsec (14 digits) + + + timestamp + 1901-12-14 + 2038-01-19 + 1 sec + + + +
+ - + + SQL92 Conventions -However, time zones in the real world can have no meaning unless -associated with a date as well as a time -since the offset may vary through the year with daylight savings -time boundaries. - + + Postgres endeavors to be compatible with + SQL92 definitions for typical usage. + However, the SQL92 standard has an odd mix of date and + time types and capabilities. Two obvious problems are: + + + + + Although the date type + does not have an associated time zone, the + time type can or does. + + + + + + The default time zone is specified as a constant integer offset + from GMT/UTC. + + + + + + Time zones in the real world can have no meaning unless + associated with a date as well as a time + since the offset may vary through the year with daylight savings + time boundaries. + - -To address these difficulties, Postgres -associates time zones only with date and time -types which contain both date and time, - and assumes local time for any type containing only -date or time. Further, time zone support is derived from -the underlying operating system -time zone capabilities, and hence can handle daylight savings time -and other expected behavior. - + + To address these difficulties, Postgres + associates time zones only with date and time + types which contain both date and time, + and assumes local time for any type containing only + date or time. Further, time zone support is derived from + the underlying operating system + time zone capabilities, and hence can handle daylight savings time + and other expected behavior. + - -In future releases, the number of date/time types will decrease, -with the current implementation of -datetime becoming timestamp, -timespan becoming interval, -and (possibly) abstime and reltime -being deprecated in favor of timestamp and interval. -The more arcane features of the date/time definitions from -the SQL92 standard are not likely to be pursued. - + + In future releases, the number of date/time types will decrease, + with the current implementation of + datetime becoming timestamp, + timespan becoming interval, + and (possibly) abstime and reltime + being deprecated in favor of timestamp and interval. + The more arcane features of the date/time definitions from + the SQL92 standard are not likely to be pursued. + + Date/Time Styles @@ -840,46 +852,71 @@ which alleviates date specification ambiguities and Y2K collation problems. - -Time Zones + + Calendar - -Postgres obtains time zone support -from the underlying operating system. -All dates and times are stored internally in Universal Coordinated Time (UTC), - alternately known as Greenwich Mean Time (GMT). -Times are converted to local time on the database server before being -sent to the client frontend, hence by default are in the server time zone. + + Postgres uses Julian dates + for all date/time calculations. They have the nice property of correctly + predicting/calculating any date more recent than something like 4013BC + to far into the future, using the assumption that the length of the + year is 365.2425 days. + - -There are several ways to affect the time zone behavior: + + Date conventions before the 19th century make for interesting reading, + but are not consistant enough to warrant coding into a date/time handler. + + - - - -The TZ environment variable used by the backend directly - on postmaster startup as the default time zone. - - - - -The PGTZ environment variable set at the client used by libpq -to send time zone information to the backend upon connection. - - - - -The SQL command SET TIME ZONE -sets the time zone for the session. - - - + + Time Zones - - If an invalid time zone is specified, -the time zone becomes GMT (on most systems anyway). - - + + Postgres obtains time zone support + from the underlying operating system for dates between 1902 and + 2038 (near the typical date limits for Unix-style + systems). Outside of this range, all dates are assumed to be + specified and used in Universal Coordinated Time (UTC). + + + + All dates and times are stored internally in Universal UTC, + alternately known as Greenwich Mean Time (GMT). + Times are converted to local time on the database server before being + sent to the client frontend, hence by default are in the server + time zone. + + + + There are several ways to affect the time zone behavior: + + + + + The TZ environment variable used by the backend directly + on postmaster startup as the default time zone. + + + + + The PGTZ environment variable set at the client used by libpq + to send time zone information to the backend upon connection. + + + + + The SQL command SET TIME ZONE + sets the time zone for the session. + + + + + + If an invalid time zone is specified, + the time zone becomes GMT (on most systems anyway). + + Date/Time Input -- 2.11.0