Time Data Types
| I l @ ve RuBoard |
| PostgreSQL includes a number of built-in data types specifically designed to handle time- and date- related data. A number of built-in constants are useful to know for simplifying date-time entry. The following is a list of them: now ”Constant that stores a timestamp upon storage. today ”Constant that refers to midnight on the current day. tomorrow ”Constant that refers to midnight on the next day. yesterday ”Constant that refers to midnight of the previous day. PostgreSQL evaluates constants at the start of a transaction, and this might result in undesired behavior. For instance, using the now constant in a series inserted inside a transaction will result in all rows having the same timestamp. A way around this is to use the now() function, which is evaluated upon each call, not during transaction creation. DATEDescriptionHolds a value that describes a particular day. Many different input formats are supported (see the following section). InputsValid range from 4713BC t o 3 2767 A D Possible input formats: June 22, 1971 ”Standard prose format of date. June 22, 200 BC ”Specifying the era. 1971-06 22 ”ISO format (yyyy-mm-dd). 6/22/ 1971 ”U.S. mode. 22/6/1971 ”European mode (not a valid date in U.S. mode). 19710622 or 710622 ”ISO format (yyyymmdd or yymmdd). 1971.174 or 71.174 ”Year and the day of the year. Storage Size4 bytes NotesValid month formats and abbreviations:
Valid days of the week and abbreviations:
The preceding describes the input formats; the output formats are specified by the DATESTYLE variable (see the SET SQL command). INTERVALDescriptionHolds a time-interval value. InputsThe input format for INTERVAL is as follows : Qnt Unit [ Qnt Unit ] Direction Valid values for Qnt are as follows: -2147483648 to +2147483648 Valid values for Unit are as follows (plurals are also valid): Second Hour Minute Day Week Month Year Decade Century Millennium Valid values for Direction are as follows: Ago ”For items in the past. [blank ] ”For future items. Storage Size12 bytes Example Data1 Week Ago 5 Years 3 Months Ago 30 Days NotesINTERVAL is accurate to a resolution of .000001 second (1 microsecond). TIMEDescriptionHolds an entry for a time-based value. InputsThe valid range for TIME is from 00: 00: 00.00 to 23: 59: 59.99 . The valid input formats that TIME can take are as follows: 08: 24 ”ISO format 08:24: 50 ”ISO format 08:24: 50.15 ”ISO format 082450 ”ISO format 08:24 PM ”Standard 20: 24 ”24-hour format z ”Same as 00:00:00 zulu ”Same as 00:00:00 Storage Size4 bytes NotesThe TIME data type is a SQL-compatible format. The TIME data type is accurate to a resolution of .000001 (1 microsecond). TIME WITH TIME ZONEDescriptionHolds an entry for a time-based value with included time-zone information. InputsThe valid range for TIME WITH TIME ZONE is from 00:00:00.00+12 to 23:59:59.99-12. The valid input formats that TIME WITH TIME ZONE can take are as follows: 08:246 ”ISO format 08:24:506 ”ISO format 08:24:50.156 ”ISO format 0824506 ”ISO format Storage Size4 bytes NotesTIME WITH TIME ZONE will accept any time-based input format that is also legal for the TIME data type, except time zone information is appended to the end. The TIME data type is a SQL-compatible format. The TIME WITH TIME ZONE data type is accurate to a resolution of .000001 (1 microsecond). TIMESTAMPDescriptionHolds values that represent time and date information. InputsThe valid range for TIMESTAMP is from 471301-01 00:00:00.00 BC to 146500112-31 23:59:59.99 AD. The valid input formats that TIMESTAMP can take are as follows: Date Time [Era] [Time Zone] For instance: 2001-11-24 08:23:11 ”Standard TIMESTAMP. 2001-11-24 08:23:11 AD -6: 00 ” with era and time zone. November 11, 2001 08:23:11 ”Prose-style TIMESTAMP. Storage Size8 bytes NotesBecause of the inclusion of time, date, era, and time-zone information, the TIMESTAMP is a popular data type for storage of temporal elements.  |
| I l @ ve RuBoard |
EAN: 2147483647
Pages: 118