From ce543b2121a772d18e25e1efbad252dcd360df96 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Tue, 14 Mar 2000 22:52:53 +0000 Subject: [PATCH] Write up new TIME WITH TIME ZONE type. --- doc/src/sgml/datatype.sgml | 216 +++++++++++++++++++++++++------------ 1 file changed, 150 insertions(+), 66 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 9bb40e73f2..1bd7d6125c 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -12,8 +12,7 @@ Postgres has a rich set of native data types available to users. Users may add new types to Postgres using the - DEFINE TYPE - command described elsewhere. + DEFINE TYPE command. @@ -80,14 +79,14 @@ exact numeric for p <= 9, s = 0 - float4/8 - float(p) - floating-point number with precision p + float4 + float(p), p < 7 + floating-point number with precision p float8 - real, double precision - double-precision floating-point number + float(p), 7 <= p < 16 + floating-point number with precision p inet @@ -109,6 +108,11 @@ signed 8-byte integer + + interval + interval + general-use time span + line @@ -155,9 +159,9 @@ time of day - timespan - interval - general-use time span + timetz + time with time zone + time of day, including time zone timestamp @@ -179,8 +183,11 @@ The cidr and inet types are designed to handle any IP type but only ipv4 is handled in the current implementation. - Everything here that talks about ipv4 will apply to ipv6 in a future release. - + Everything here that talks about ipv4 will apply to ipv6 in a + future release. + + + @@ -230,7 +237,8 @@ which are ill considered and which should not live through subsequent standards. Postgres will not make great efforts to conform to these features; however, these tend to apply in little-used - or obsure cases, and a typical user is not likely to run into them. + or obsure cases, and a typical user is not likely to run into them. + Most of the input and output functions corresponding to the @@ -244,7 +252,7 @@ - Note that some of the input and output functions are not invertible. That is, + Some of the input and output functions are not invertible. That is, the result of an output function may lose precision when compared to the original input. @@ -256,7 +264,8 @@ most of the intrinsic precision of the type (typically 15 digits for doubles, 6 digits for 4-byte floats). Other types with underlying floating point fields (e.g. geometric - types) carry similar precision. + types) carry similar precision. + @@ -390,7 +399,6 @@ CREATE TABLE tablename (DROP SEQUENCE. - @@ -399,8 +407,9 @@ CREATE TABLE tablename ( Obsolete Type - The money is now obsolete. Use numeric - or decimal instead. + The money is now deprecated. Use numeric + or decimal instead. The money type may become a + locale-aware layer over the numeric type in a future release. @@ -453,8 +462,8 @@ CREATE TABLE tablename (Postgres supports these types, in addition to the more general text type, which unlike varchar - does not require an upper - limit to be declared on the size of the field. + does not require an explicit declared upper + limit on the size of the field. @@ -501,15 +510,15 @@ CREATE TABLE tablename ( - There is one other fixed-length character type. + There is one other fixed-length character type in Postgres. The name type - only has one purpose and that is to provide - Postgres with a - special type to use for internal names. + only has one purpose and that is for storage of internal catalog + names. It is not intended for use by the general user. - It's length is currently defined as 32 chars + Its length is currently defined as 32 bytes (31 characters plus terminator) but should be reference using NAMEDATALEN. - This is set at compile time and may change in a future release. + The length is set at compile time (and is therefore adjustable for + special uses); the default maximum length may change in a future release. @@ -528,7 +537,7 @@ CREATE TABLE tablename ( name 32 bytes - Thirty-two character internal type + Thirty-one character internal type @@ -569,6 +578,14 @@ CREATE TABLE tablename (AD 1465001 1 microsec / 14 digits + + timestamp with time zone + date and time including time zone + 8 bytes + 1903 AD + 2037 AD + 1 microsec / 14 digits + interval for time intervals @@ -593,6 +610,14 @@ CREATE TABLE tablename (23:59:59.99 1 microsecond + + time with time zone + times of the day + 4 bytes + 00:00:00.00+12 + 23:59:59.99-12 + 1 microsecond +
@@ -603,8 +628,9 @@ CREATE TABLE tablename (datetime (equivalent to timestamp) and timespan (equivalent to interval). The types abstime and reltime are lower precision types which are used internally. - You are discouraged from using any of these types in new applications and move any old - ones over when appropriate. Any or all of these type might disappear in a future release. + You are discouraged from using any of these types in new + applications and are encouraged to move any old + ones over when appropriate. Any or all of these types might disappear in a future release. @@ -615,18 +641,19 @@ CREATE TABLE tablename ( Date and time input is accepted in almost any reasonable format, including - ISO-compatible, SQL-compatible, + ISO-8601, SQL-compatible, traditional Postgres, and others. The ordering of month and day in date input can be ambiguous, therefore a setting - exists, to specify how it should be interpreted. The command + exists to specify how it should be interpreted. The command SET DateStyle TO 'US' or SET DateStyle TO 'NonEuropean' specifies the variant month before day, the command SET DateStyle TO 'European' sets the variant - day before month. The former is the default. + day before month. The US style + is the default but this default can be changed at compile time or at run time. - See + See for the exact parsing rules of date/time input and for the recognized time zones. @@ -639,6 +666,7 @@ CREATE TABLE tablename (date The following are possible inputs for the date type. + <productname>PostgreSQL</productname> Date InputDate Inputs @@ -813,13 +841,84 @@ CREATE TABLE tablename ( - time - - The following are valid time inputs. -
- <productname>PostgreSQL</productname> Time Input - Time Inputs - + time + + The following are valid time inputs. + +
+ <productname>PostgreSQL</productname> Time Input + Time Inputs + + + + Example + Description + + + + + 04:05:06.789 + ISO-8601 + + + 04:05:06 + ISO-8601 + + + 04:05 + ISO-8601 + + + 040506 + ISO-8601 + + + 04:05 AM + Same as 04:05; AM does not affect value + + + 04:05 PM + Same as 16:05; input hour must be <= 12 + + + z + Same as 00:00:00 + + + zulu + Same as 00:00:00 + + + allballs + Same as 00:00:00 + + + +
+ + + + + time with time zone + + This type is defined by SQL92, but the definition exhibits + fundamental deficiencies which renders the type near useless. In + most cases, a combination of date, + time, and timestamp with time zone + should provide a complete range of date/time functionality + required by an application. + + + + time with time zone accepts all input also legal + for the time type, appended with a legal time zone, + as follows: + + + <productname>PostgreSQL</productname> Time With Time + Zone Input + Time With Time Zone Inputs + Example @@ -828,45 +927,30 @@ CREATE TABLE tablename ( - 04:05:06.789 + 04:05:06.789-8 ISO-8601 - 04:05:06 + 04:05:06-08:00 ISO-8601 - 04:05 + 04:05-08:00 ISO-8601 - 040506 + 040506-08/entry> ISO-8601 - - 04:05 AM - Same as 04:05; AM does not affect value - - - 04:05 PM - Same as 16:05; input hour must be <= 12 - - - z - Same as 00:00:00 - - - zulu - Same as 00:00:00 - - - allballs - Same as 00:00:00 -
+ + + Refer to for + more examples of time zones. + @@ -888,8 +972,8 @@ January 8 04:05:06 1999 PST - - <productname>PostgreSQL</productname> Time Zone Input +
+ <productname>PostgreSQL</productname> Time Zone InputTime Zone Inputs -- 2.40.0