Date/Time Types
PostgreSQL supports the full set of SQL date and time types.
Table 2-8. Date/Time Types
| Type | Description | Storage | Earliest | Latest | Resolution |
|---|---|---|---|---|---|
| date | dates only | 4 bytes | 4713 BC | AD 32767 | 1 day |
| time [(p)] [without time zone] | times of day only | 8 bytes | 00:00:00.00 | 23:59:59.99 | 1 microsecond |
| time [(p)] with time zone | times of day only | 12 bytes | 00:00:00.00+12 | 23:59:59.99-12 | 1 microsecond |
| timestamp [(p)] [with time zone] | date and time with time zone | 8 bytes | 4713 AD | AD 1465001 | 1 microsecond / 14 digits |
| timestamp [(p)] [without time zone] | both date and time | 8 bytes | 4713 BC | AD 1465001 | 1 microsecond / 14 digits |
| interval [(p)] | for time intervals | 12 bytes | -178000000 years | 178000000 years | 1 microsecond |
time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The effective limit of precision is determined by the underlying double precision floating-point number used to store values (in seconds for interval and in seconds since 2000-01-01 for timestamp). The useful range of p is from 0 to about 6 for timestamp, but may be more for interval. The system will accept p ranging from 0 to 13.
Time zones, and time-zone conventions, are influenced by political decisions, not just earth geometry. Time zones around the world became somewhat standardized during the 1900's, but continue to be prone to arbitrary changes. PostgreSQL uses Linux's underlying features to provide output time-zone support, and these systems usually contain information for only the time period 1902 through 2038 (corresponding to the full range of conventional Unix system time). timestamp with time zone and time with time zone will use time zone information only within that year range, and assume that times outside that range are in UTC.
 | To ensure compatibility with earlier versions of PostgreSQL, we also continue to provide datetime (equivalent to timestamp) and timespan (equivalent to interval). However, support for these types is now restricted to having an implicit translation to timestamp and interval. You should move any old types over when appropriate and you should not use any of these types in new applications. |
The types abstime and reltime are lower precision types that are used internally. You are discouraged from using these types.
Date/Time Input
Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional PostgreSQL, and others. 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". The ISO style is the default, but this default can be changed at compile time or at run time.
PostgreSQL is more flexible in handling date/time than the SQL standard requires.
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. SQL9x requires the following syntax
type [ (p) ] 'value' |
date
The following are possible inputs for the date type.
Table 2-9. 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 |
| J2451187 | Julian day |
| January 8, 99 BC | Year 99 before the Common Era |
Date/Time Keywords
Table 2-10. 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. |
Table 2-11. Day of the Week Abbreviations
| Day | Abbreviation |
|---|---|
| Sunday | Sun |
| Monday | Mon |
| Tuesday | Tue, Tues |
| Wednesday | Wed, Weds |
| Thursday | Thu, Thur, Thurs |
| Friday | Fri |
| Saturday | Sat |
time [ ( p ) ] [ without time zone ]
This type can be referenced as time and as time without time zone, as per SQL99. The optional precision p should be between 0 and 13, and defaults to the precision of the input time literal.
The following are valid time inputs.
Table 2-12. Time Input Examples
| 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 |
| allballs | Same as 00:00:00 |
time [ ( p ) ] 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.
The optional precision p should be between 0 and 13, and defaults to the precision of the input time literal.
time with time zone accepts all input also legal for the time type, appended with a legal time zone, as follows:
Table 2-13. 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-14 for more examples of time zones.
timestamp [ (p) ] 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 |
The optional precision p should be between 0 and 13, and defaults to the precision of the input timestamp literal.
Table 2-14. Time Zone Input
| 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 |
timestamp [ (p) ] without time zone
Valid input for the timestamp without time zone 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:
2002-01-08 04:05:06 |
January 8 04:05:06 2002 PST |
The optional precision p should be between 0 and 13, and defaults to the precision of the input timestamp literal.
For timestamp without time zone, any explicit time zone specified in the input is silently swallowed. That is, the resulting date/time value is derived from the explicit date/time fields in the input value, and is not adjusted for time zone.
interval [ ( p ) ]
The interval type can be specified with the following syntax:
Quantity Unit [Quantity Unit...] [Direction] @ Quantity Unit [Quantity Unit...] [Direction] |
Quantities of days, hours, minutes, and seconds can be specified without explicit unit markings. For example, '1 12:59:10' is read the same as '1 day 12 hours 59 min 10 sec'.
The optional precision p should be between 0 and 13, and defaults to the precision of the input literal.
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. The latter two accept an optional precision specification.
PostgreSQL also supports several special constants for convenience.
Table 2-15. 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 |
| zulu, allballs, z | 00:00:00.00 GMT |
'now' is evaluated when the value is first interpreted.
| As of PostgreSQL version 7.2, 'current' is no longer supported as a date/time constant. Previously, 'current' was stored as a special value, and evaluated to 'now' only when used in an expression or type conversion. |
Date/Time Input Interpretation
The date/time types are all decoded using a common set of routines.
Date/Time Decoding
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.
Date/Time Output
Output formats can be set to one of the four styles ISO 8601, SQL (Ingres), traditional PostgreSQL, and German, using the SET DateStyle. The default is the ISO format.
Table 2-16. 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 |
| 'PostgreSQL' | 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 the Section called Date/Time Input for how this setting affects interpretation of input values.)
Table 2-17. 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 did not 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, if set at the client, is used by libpq to send a SET TIME ZONE command 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'
| If an invalid time zone is specified, the time zone becomes GMT. |
| If the runtime option AUSTRALIAN_TIMEZONES is set then CST and EST refer to Australian time zones, not American ones. |
PostgreSQL must have internal tabular information for time zone decoding, since there is no Unix standard system interface to provide access to general, cross-timezone information. The underlying OS is used to provide time zone information for output.
The following table of time zones recognized by PostgreSQL is organized by time zone offset from UTC, rather than alphabetically; this is intended to facilitate matching local usage with recognized abbreviations for cases where these might differ.
Table 2-18. 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 the runtime option AUSTRALIAN_TIMEZONES is set then CST, EST, and SAT will be interpreted as Australian timezone names. Without this option, CST and EST are taken as American timezone names, while SAT is interpreted as a noise word indicating Saturday.
Table 2-19. Postgres Australian Time Zones
| Time Zone | Offset from UTC | Description |
|---|---|---|
| CST | +10:30 | Australian Central Standard Time |
| EST | +10:00 | Australian Eastern Standard Time |
| SAT | +9:30 | South Australian Std Time |