Date/Time Types
PostgreSQL supports the full set of SQL date and time types.
Table 2-4. Date/Time Types
| Type | Description | Storage | Earliest | Latest | Resolution |
|---|---|---|---|---|---|
| timestamp | both date and time | 8 bytes | 4713 BC | AD 1465001 | 1 microsecond / 14 digits |
| timestamp [ with time zone ] | date and time with time zone | 8 bytes | 1903 AD | 2037 AD | 1 microsecond / 14 digits |
| interval | for time intervals | 12 bytes | -178000000 years | 178000000 years | 1 microsecond |
| date | dates only | 4 bytes | 4713 BC | 32767 AD | 1 day |
| time [ without time zone ] | times of day only | 4 bytes | 00:00:00.00 | 23:59:59.99 | 1 microsecond |
| time with time zone | times of day only | 4 bytes | 00:00:00.00+12 | 23:59:59.99-12 | 1 microsecond |
 | To ensure compatibility to earlier versions of PostgreSQL we also continue to provide datetime (equivalent to timestamp) and timespan (equivalent to interval), however support for these is now restricted to having an implicit translation to timestamp and interval. You are discouraged from using any of these types in new applications and are encouraged to move any old ones over when appropriate. |
Date/Time Input
Date and time input is accepted in almost any reasonable format, including ISO-8601, SQL-compatible, traditional PostgreSQL, and others. The ISO style is the default but this default can be changed at compile time or at run time. In the SQL style, the ordering of month and day in date input can be ambiguous, therefore a setting exists to specify how it should be interpreted in ambiguous cases. 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".
See the Section called Date/Time Input Interpretation for the exact parsing rules of date/time input and the Section called Time Zones for the recognized time zones.
Remember that any date or time input needs to be enclosed into single quotes, like text strings. Refer to the Section called Constants of Other Types in Chapter 1 for more information.
date
The following are possible inputs for the date type.
Table 2-5. Date Input
| Example | Description |
|---|---|
| January 8, 1999 | Unambiguous |
| 1999-01-08 | ISO-8601 format, preferred |
| 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 |
| 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 Common Era |
Table 2-6. 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. |
time [ without time zone ]
Per SQL99, this type can be referenced as time and as time without time zone.
The following are valid time inputs.
time with time zone
This type is defined by SQL92, but the definition exhibits fundamental deficiencies that render the type nearly useless. In most cases, a combination of date, time, and timestamp should provide a complete range of date/time functionality required by any application.
time with time zone accepts all input also legal for the time type, appended with a legal time zone, as follows:
Table 2-9. Time With Time Zone Input Examples
| Example | Description |
|---|---|
| 04:05:06.789-8 | ISO-8601 |
| 04:05:06-08:00 | ISO-8601 |
| 04:05-08:00 | ISO-8601 |
| 040506-08 | ISO-8601 |
Refer to Table 2-10 for more examples of time zones.
timestamp with time zone
Valid input for the timestamp type consists of a concatenation of a date and a time, followed by an optional AD or BC, followed by an optional time zone. (See below.) Thus
1999-01-08 04:05:06 -8:00
|
January 8 04:05:06 1999 PST
|
interval
The interval type can be specified with the following syntax:
Quantity Unit [Quantity Unit...] [Direction]
@ Quantity Unit [Direction]
|
Special values
The following SQL-compatible functions can be used as date or time input for the corresponding data type:
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
PostgreSQL also supports several special constants for convenience.
Table 2-11. Special Date/Time 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 |
Date/Time Input Interpretation
The date/time types are all decoded using a common set of routines.
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 (for example, "19990113" for January 13, 1999) or time (for example, 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 (for example, today), day (for example, Thursday), month (for example, January), or optional word (for example, 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" (for example, 19990118). 8 and 6 digits are interpreted as year, month, and day, while 7 and 5 digits are interpreted as year, day of year, respectively.
If the token is three digits and a year has already been decoded, then interpret as day of year.
If four or more digits, then interpret as a year.
If in European date mode, and if the day field has not yet been read, and if the value is less than or equal to 31, then interpret as a day.
If the month field has not yet been read, and if the value is less than or equal to 12, then interpret as a month.
If the day field has not yet been read, and if the value is less than or equal to 31, then interpret as a day.
If two digits or four or more digits, then interpret as a year.
Otherwise, throw an error.
If BC has been specified, negate the year and offset by one for internal storage (there is no year zero in the Gregorian calendar, so numerically 1BC becomes year zero).
If BC was not specified, and if the year field was two digits in length, then adjust the year to 4 digits. If the field was less than 70, then add 2000; otherwise, add 1900.
Gregorian years 1-99AD may be entered by using 4 digits with leading zeros (for example, 0099 is 99AD). Previous versions of PostgreSQL accepted years with three digits and with single digits, but as of version 7.0 the rules have been tightened up to reduce the possibility of ambiguity. PostgreSQL follows the latter rules.
Date/Time Output
Output formats can be set to one of the four styles ISO-8601, SQL (Ingres), traditional Postgres, and German, using the SET DateStyle. The default is the ISO format.
Table 2-12. Date/Time Output Styles
| Style Specification | Description | Example |
|---|---|---|
| 'ISO' | ISO-8601 standard | 1997-12-17 07:37:16-08 |
| 'SQL' | Traditional style | 12/17/1997 07:37:16.00 PST |
| 'Postgres' | Original style | Wed Dec 17 07:37:16 1997 PST |
| 'German' | Regional style | 17.12.1997 07:37:16.00 PST |
The output of the date and time styles is the date or time part, in accordance with the above examples.
The SQL style has European and non-European (US) variants, which determines whether month follows day or vice versa. (See also above at Date/Time Input, how this setting affects interpretation of input values.)
Table 2-13. Date Order Conventions
| Style Specification | Description | Example |
|---|---|---|
| European | day/month/year | 17/12/1997 15:37:16.00 MET |
| US | month/day/year | 12/17/1997 07:37:16.00 PST |
interval output looks like the input format, except that units like week or century are converted to years and days. In ISO mode the output looks like
[ Quantity Units [ ... ] ] [ Days ] Hours:Minutes [ ago ] |
There are several ways to affect the appearance of date/time types:
The PGDATESTYLE environment variable used by the backend directly on postmaster start-up.
The PGDATESTYLE environment variable used by the frontend libpq on session start-up.
SET DATESTYLE SQL command.
Date/Time Calculations
PostgreSQL uses Julian dates for all date/time calculations. They have the nice property of correctly predicting/calculating any date more recent than 4713BC to far into the future, using the assumption that the length of the year is 365.2425 days.
The Julian Day was invented by the French scholar Joseph Justus Scaliger (1540-1609) and probably takes its name from the Scaliger's father, the Italian scholar Julius Caesar Scaliger (1484-1558). Astronomers have used the Julian period to assign a unique number to every day since 1 January 4713 BC. This is the so-called Julian Day (JD). JD 0 designates the 24 hours from noon UTC on 1 January 4713 BC to noon UTC on 2 January 4713 BC.
"Julian Day" is different from "Julian Date". The Julian calendar was introduced by Julius Caesar in 45 BC. It was in common use until the 1582, when countries started changing to the Gregorian calendar. In the Julian calendar, the tropical year is approximated as 365 1/4 days = 365.25 days. This gives an error of about 1 day in 128 years. The accumulating calendar error prompted Pope Gregory XIII to reform the calendar in accordance with instructions from the Council of Trent.
In the Gregorian calendar, the tropical year is approximated as 365 + 97 / 400 days = 365.2425 days. Thus it takes approximately 3300 years for the tropical year to shift one day with respect to the Gregorian calendar.
The approximation 365+97/400 is achieved by having 97 leap years every 400 years, using the following rules:
| Every year divisible by 4 is a leap year. |
| However, every year divisible by 100 is not a leap year. |
| However, every year divisible by 400 is a leap year after all. |
The Papal bull of February 1582 decreed that 10 days should be dropped from October 1582 so that 15 October should follow immediately after 4 October. This was observed in Italy, Poland, Portugal, and Spain. Other Catholic countries followed shortly after, but Protestant countries were reluctant to change, and the Greek orthodox countries didn't change until the start of this century. The reform was observed by Great Britain and Dominions (including what is now the USA) in 1752. Thus 2 Sep 1752 was followed by 14 Sep 1752. This is why UNIX systems have cal produce the following:
% cal 9 1752
September 1752
S M Tu W Th F S
1 2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
|
| The SQL92 standard states that "Within the definition of a datetime literal, the datetime values are constrained by the natural rules for dates and times according to the Gregorian calendar". Dates between 1752-09-03 and 1752-09-13, although eliminated in some countries by Papal fiat, conform to "natural rules" and are hence valid dates. |
Different calendars have been developed in various parts of the world, many predating the Gregorian system. For example, the beginnings of the Chinese calendar can be traced back to the 14th century BC. Legend has it that the Emperor Huangdi invented the calendar in 2637 BC. The People's Republic of China uses the Gregorian calendar for civil purposes. Chinese calendar is used for determining festivals.
Time Zones
PostgreSQL endeavors to be compatible with SQL92 definitions for default usage. There are two exceptions:
Although the date type does not have an associated time zone, the time type can or does. 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.
The default time zone is specified as a constant integer offset from GMT/UTC. It is not possible to adapt to daylight savings time when doing date/time arithmetic across DST boundaries.
To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We recommend not using the SQL92 type TIME WITH TIME ZONE (although it is supported by PostgreSQL for legacy applications and for compatibility with other RDBMS implementations). PostgreSQL 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.
PostgreSQL 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 UTC, traditionally 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 is used by the backend directly on postmaster start-up 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.
The SQL92 qualifier on
where zone can be specified as a text time zone (for example. 'PST') or as an interval (for example, INTERVAL '-08:00').timestamp AT TIME ZONE 'zone'
| On most systems, if you specify an invalid time zone, the time zone becomes GMT. |
| If PostgreSQL was compiled so that the compiler option USE_AUSTRALIAN_RULES is set, then EST refers to Australia Eastern Standard Time, which has an offset of +10:00 hours from UTC. The PostgreSQL–supplied binaries are not compiled with this option. |
PostgreSQL must have internal tabular information for time zone decoding, since there is no *nix standard system interface to provide access to general, cross-timezone information. The underlying OS is used to provide time zone information for output.
Table 2-14. PostgreSQL Recognized Time Zones
| Time Zone | Offset from UTC | Description |
|---|---|---|
| NZDT | +13:00 | New Zealand Daylight Time |
| IDLE | +12:00 | International Date Line, East |
| NZST | +12:00 | New Zealand Std Time |
| NZT | +12:00 | New Zealand Time |
| AESST | +11:00 | Australia Eastern Summer Std Time |
| ACSST | +10:30 | Central Australia Summer Std Time |
| CADT | +10:30 | Central Australia Daylight Savings Time |
| SADT | +10:30 | South Australian Daylight Time |
| AEST | +10:00 | Australia Eastern Std Time |
| EAST | +10:00 | East Australian Std Time |
| GST | +10:00 | Guam Std Time, USSR Zone 9 |
| LIGT | +10:00 | Melbourne, Australia |
| ACST | +09:30 | Central Australia Std Time |
| SAST | +09:30 | South Australia Std Time |
| CAST | +09:30 | Central Australia Std Time |
| AWSST | +9:00 | Australia Western Summer Std Time |
| JST | +9:00 | Japan Std Time,USSR Zone 8 |
| KST | +9:00 | Korea Standard Time |
| WDT | +9:00 | West Australian Daylight Time |
| MT | +8:30 | Moluccas Time |
| AWST | +8:00 | Australia Western Std Time |
| CCT | +8:00 | China Coastal Time |
| WADT | +8:00 | West Australian Daylight Time |
| WST | +8:00 | West Australian Std Time |
| JT | +7:30 | Java Time |
| WAST | +7:00 | West Australian Std Time |
| IT | +3:30 | Iran Time |
| BT | +3:00 | Baghdad Time |
| EETDST | +3:00 | Eastern Europe Daylight Savings Time |
| CETDST | +2:00 | Central European Daylight Savings Time |
| EET | +2:00 | Eastern Europe, USSR Zone 1 |
| FWT | +2:00 | French Winter Time |
| IST | +2:00 | Israel Std Time |
| MEST | +2:00 | Middle Europe Summer Time |
| METDST | +2:00 | Middle Europe Daylight Time |
| SST | +2:00 | Swedish Summer Time |
| BST | +1:00 | British Summer Time |
| CET | +1:00 | Central European Time |
| DNT | +1:00 | Dansk Normal Tid |
| FST | +1:00 | French Summer Time |
| MET | +1:00 | Middle Europe Time |
| MEWT | +1:00 | Middle Europe Winter Time |
| MEZ | +1:00 | Middle Europe Zone |
| NOR | +1:00 | Norway Standard Time |
| SET | +1:00 | Seychelles Time |
| SWT | +1:00 | Swedish Winter Time |
| WETDST | +1:00 | Western Europe Daylight Savings Time |
| GMT | 0:00 | Greenwich Mean Time |
| WET | 0:00 | Western Europe |
| WAT | -1:00 | West Africa Time |
| NDT | -2:30 | Newfoundland Daylight Time |
| ADT | -03:00 | Atlantic Daylight Time |
| NFT | -3:30 | Newfoundland Standard Time |
| NST | -3:30 | Newfoundland Standard Time |
| AST | -4:00 | Atlantic Std Time (Canada) |
| EDT | -4:00 | Eastern Daylight Time |
| CDT | -5:00 | Central Daylight Time |
| EST | -5:00 | Eastern Standard Time |
| CST | -6:00 | Central Std Time |
| MDT | -6:00 | Mountain Daylight Time |
| MST | -7:00 | Mountain Standard Time |
| PDT | -7:00 | Pacific Daylight Time |
| PST | -8:00 | Pacific Std Time |
| YDT | -8:00 | Yukon Daylight Time |
| HDT | -9:00 | Hawaii/Alaska Daylight Time |
| YST | -9:00 | Yukon Standard Time |
| AHST | -10:00 | Alaska-Hawaii Std Time |
| CAT | -10:00 | Central Alaska Time |
| NT | -11:00 | Nome Time |
| IDLW | -12:00 | International Date Line, West |
Australian Time Zones
Australian time zones and their naming variants account for fully one quarter of all time zones in the PostgreSQL time zone lookup table. There are two naming conflicts with common time zones defined in the United States, CST and EST.
If PostgreSQL was compiled with the option USE_AUSTRALIAN_RULES, then CST, EST, and SAT will be interpreted using Australian conventions. Without this option, SAT is interpreted as a optional word indicating "Saturday". The binary in the PostgreSQL standard distribution does not have this option set.