From c00d800e2dbb475f1b1324c155c2a06835d16f49 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Tue, 19 Jan 1999 16:08:26 +0000 Subject: [PATCH] Add tables of allowed values and formats for date/time input types. Include info for time zones, days of week, months. Procedural description of date input parsing is not yet complete. --- doc/src/sgml/datatype.sgml | 946 +++++++++++++++++++++++++++++++++---- 1 file changed, 859 insertions(+), 87 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 457a988484..1a8dcadda4 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -881,29 +881,29 @@ the time zone becomes GMT (on most systems anyway). - -Date/Time Input + + Date/Time Input - -General-use date and time is input using a wide range of -styles, including ISO-compatible, SQL-compatible, -traditional Postgres -and other permutations of date and time. In cases where interpretation -can be ambiguous (quite possible with many traditional styles of date -specification) Postgres uses a style setting -to resolve the ambiguity. - + + General-use date and time is input using a wide range of + styles, including ISO-compatible, SQL-compatible, + traditional Postgres + and other permutations of date and time. In cases where interpretation + can be ambiguous (quite possible with many traditional styles of date + specification) Postgres uses a style setting + to resolve the ambiguity. + - -Most date and time types share code for data input. For those types -the input can have any of a wide variety of styles. For numeric date -representations, -European and US conventions can differ, and the proper interpretation -is obtained by using the SET DATESTYLE -command before entering data. -Note that the style setting does not preclude use of various styles for input; - it is used primarily to determine the output style and to resolve ambiguities. - + + Most date and time types share code for data input. For those types + the input can have any of a wide variety of styles. For numeric date + representations, + European and US conventions can differ, and the proper interpretation + is obtained by using the SET DATESTYLE + command before entering data. + Note that the style setting does not preclude use of various styles for input; + it is used primarily to determine the output style and to resolve ambiguities. + The special values current, @@ -915,74 +915,846 @@ Note that the style setting does not preclude use of various styles for input; - The strings - now, - today, - yesterday, - tomorrow, - and epoch - can be used to specify - time values. now - means the current transaction time, and differs from - current - in that the current time is immediately substituted - for it. epoch - means Jan 1 00:00:00 1970 GMT. - + The strings + now, + today, + yesterday, + tomorrow, + and epoch + can be used to specify time values. + now + means the current transaction time, and differs from + current + in that the current time is immediately substituted for it. + epoch means Jan 1 00:00:00 1970 GMT. + - - -<productname>Postgres</productname> Date/Time Special Constants -Constants - - - - Constant - Description - - - - - current - Current transaction time, deferred - - - epoch - 1970-01-01 00:00:00+00 (Unix system time zero) - - - infinity - Later than other valid times - - - -infinity - Earlier than other valid times - - - invalid - Illegal entry - - - now - Current transaction time - - - today - Midnight today - - - tomorrow - Midnight tomorrow - - - yesterday - Midnight yesterday - - - -
- + + + <productname>Postgres</productname> Date/Time Special Constants + Constants + + + + Constant + Description + + + + + current + Current transaction time, deferred + + + epoch + 1970-01-01 00:00:00+00 (Unix system time zero) + + + infinity + Later than other valid times + + + -infinity + Earlier than other valid times + + + invalid + Illegal entry + + + now + Current transaction time + + + today + Midnight today + + + tomorrow + Midnight tomorrow + + + yesterday + Midnight yesterday + + + +
+ + + + + <productname>Postgres</productname> Date Input + Date Inputs + + + + Example + Description + + + + + January 8, 1999 + Unambiguous text month + + + 1999-01-08 + ISO-8601 + + + 1/8/1999 + US; read as August 1 in European mode + + + 8/1/1999 + European; read as August 1 in US mode + + + 1/18/1999 + US; read as January 18 in any mode + + + 1999.008 + Year and day of year + + + 19990108 + ISO-8601 year, month, day + + + 990108 + ISO-8601 year, month, day + + + 1999.008 + Year and day of year + + + 99008 + Year and day of year + + + January 8, 99 BC + Year 99 before the Christian Era + + + +
+ + + + + <productname>Postgres</productname> Month Abbreviations + Month Abbreviations + + + + Month + Abbreviations + + + + + April + Apr + + + August + Aug + + + December + Dec + + + February + Feb + + + January + Jan + + + July + Jul + + + June + Jun + + + March + Mar + + + November + Nov + + + October + Oct + + + September + Sep, Sept + + + +
+ + + + The month May has no explicit abbreviation, for obvious reasons. + + + + + + + <productname>Postgres</productname> Day of Week Abbreviations + Day of Week Abbreviations + + + + Day + Abbreviation + + + + + Sunday + Sun + + + Monday + Mon + + + Tuesday + Tue, Tues + + + Wednesday + Wed, Weds + + + Thursday + Thu, Thur, Thurs + + + Friday + Fri + + + Saturday + Sat + + + +
+ + + + + <productname>Postgres</productname> Time Input + Time Inputs + + + + Example + Description + + + + + 04:05:06.789 + ISO-8601, with all time fields + + + 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 + + + +
+ + + + + <productname>Postgres</productname> Time Zone Input + Time Zone Inputs + + + + Time Zone + Description + + + + + PST + Pacific Standard Time + + + -8:00 + ISO-8601 offset for PST + + + -800 + ISO-8601 offset for PST + + + -8 + ISO-8601 offset for PST + + + +
+ + + + + <productname>Postgres</productname> Recognized Time Zones + Time Zones + + + + Time Zone + Offset from UTC + Description + + + + + ACSST + +10:30 + Central Australia Summer Std Time + + + ACST + +09:30 + Central Australia Std Time + + + ADT + -02:00 + Atlantic Daylight Time + + + AESST + +11:00 + Australia Eastern Summer Std Time + + + AEST + +10:00 + Australia Eastern Std Time + + + AHST + -10:00 + Alaska-Hawaii Std Time + + + AST + -4:00 + Atlantic Std Time (Canada) + + + AWSST + +9:00 + Australia Western Summer Std Time + + + AWST + +8:00 + Australia Western Std Time + + + BST + +1:00 + British Summer Time + + + BT + +3:00 + Baghdad Time + + + CADT + +10:30 + Central Australia Daylight Savings Time + + + CAST + +09:30 + Central Australia Std Time + + + CAT + -10:00 + Central Alaska Time + + + CCT + +8:00 + China Coastal Time + + + CDT + -5:00 + Central Daylight Time + + + CET + +1:00 + Central European Time + + + CETDST + +2:00 + Central European Daylight Savings Time + + + CST + +6:00 + Central Std Time + + + DNT + +1:00 + Dansk Normal Tid + + + DST + +1:00 + Dansk Standard Time (?) + + + EAST + +10:00 + East Australian Std Time + + + EDT + -4:00 + Eastern Daylight Time + + + EET + +2:00 + Eastern Europe, USSR Zone 1 + + + EETDST + +3:00 + Eastern Europe Daylight Savings Time + + + EST + -5:00 + Eastern Standard Time + + + FST + +1:00 + French Summer Time + + + FWT + +2:00 + French Winter Time + + + GMT + 0:00 + Greenwish Mean Time + + + GST + +10:00 + Guam Std Time, USSR Zone 9 + + + HDT + -9:00 + Hawaii/Alaska + + + IDLE + +12:00 + International Date Line, East + + + IDLW + -12:00 + International Date Line, West + + + IST + +2:00 + Israel + + + IT + +3:40 + Iran Time + + + JST + +9:00 + Japan Std Time,USSR Zone 8 + + + JT + +7:30 + Java Time + + + KST + +9:00 + Korea Standard Time + + + LIGT + +10:00 + Melbourne, Australia + + + MDT + -6:00 + Mountain Daylight Time + + + MEST + +2:00 + Middle Europe Summer Time + + + MET + +1:00 + Middle Europe Time + + + METDST + +2:00 + Middle Europe Daylight Time + + + MEWT + +1:00 + Middle Europe Winter Time + + + MEZ + +1:00 + Middle Europe Zone + + + MST + -7:00 + Mountain Standard Time + + + MT + +8:30 + Moluccas Time + + + NDT + -2:30 + Newfoundland Daylight Time + + + NFT + -3:30 + Newfoundland Standard Time + + + NOR + +1:00 + Norway Standard Time + + + NST + -3:30 + Newfoundland Standard Time + + + NT + -11:00 + Nome Time + + + NZDT + +13:00 + New Zealand Daylight Time + + + NZST + +12:00 + New Zealand Std Time + + + NZT + +12:00 + New Zealand Time + + + PDT + -7:00 + Pacific Daylight Time + + + PST + -8:00 + Pacific Std Time + + + SADT + +10:30 + South Australian Daylight Time + + + SAT + +9:30 + South Australian Std Time + + + SET + +1:00 + Seychelles Time + + + SST + +2:00 + Swedish Summer Time + + + SWT + +1:00 + Swedish Winter Time + + + WADT + +8:00 + West Australian Daylight Time + + + WAST + +7:00 + West Australian Std Time + + + WAT + -1:00 + West Africa Time + + + WDT + +9:00 + West Australian Daylight Time + + + WET + 0:00 + Western Europe + + + WETDST + +1:00 + Western Europe Daylight Savings Time + + + WST + +8:00 + West Australian Std Time + + + YDT + -8:00 + Yukon Daylight Time + + + YST + -9:00 + Yukon Standard Time + + + ZP4 + -4:00 + GMT +4 hours + + + ZP5 + -5:00 + GMT +5 hours + + + ZP6 + -6:00 + GMT +6 hours + + + +
+ + + If the compiler option USE_AUSTRALIAN_RULES is set + then EST refers to Australia Eastern Std Time, + which has an offset of +10:00 hours from UTC. + + + + It is interesting that Australian time zones and their naming variants + account for fully one quarter of all time zones in the + Postgres time zone lookup table. + + + + + Date/Time Input Interpretation + + + Break the input string into tokens and categorize each token as + a string, time, time zone, or number. + + + + + + If the token contains a colon (":"), this is a time string. + + + + + + If the token contains a dash ("-"), slash ("/"), or dot ("."), + this is a date string which may have a text month. + + + + + + If the token is numeric only, then it is either a single field + or an ISO-8601 concatenated date (e.g. "19990113" for January 13, 1999) + or time (e.g. 141516 for 14:15:16). + + + + + If the token starts with a plus ("+") or minus ("-"), + then it is either a time zone or a special field. + + + + + + + + If the token is a text string, match up with possible strings. + + + + + + Do a binary-search table lookup for the token + as either a special string (e.g. today), + day (e.g. Thursday), + month (e.g. January), or noise word (e.g. on). + + + Set field values and bit mask for fields. + For example, set year, month, day for today, and additionally + hour, minute, second for now. + + + + + + If not found, do a similar binary-search table lookup to match + the token with a time zone. + + + + + + If not found, throw an error. + + + + + + + + The token is a number or number field. If there are more than 4 digits, + and if no other date fields have been previously read, then interpret + as a "concatenated date". + + + + + If there + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + -- 2.40.0