Date/Time Values

2017-11-03 09:05:05

Date Time Values

PostgreSQL supports four basic temporal data types plus a couple of extensions that deal with time zone issues.

The DATE type is used to store dates. A DATE value stores a century, year, month, and day.

The TIME data type is used to store a time-of-day value. A TIME value stores hours, minutes, seconds, and microseconds. It is important to note that a TIME value does not contain a time zoneif you want to include a time zone, you should use the type TIME WITH TIME ZONE. TIMETZ is a synonym for TIME WITH TIME ZONE.

The TIMESTAMP data type combines a DATE and a TIME, storing a century, year, month, day, hour, minutes, seconds, and microseconds. Unlike the TIME data type, a TIMESTAMP does include a time zone. If, for some reason, you want a date/time value that does not include a time zone, you can use the type TIMESTAMP WITHOUT TIME ZONE.

The last temporal data type is the INTERVAL. An INTERVAL represents a span of time. I find that the easiest way to think about INTERVAL values is to remember that an INTERVAL stores some (possibly large) number of seconds, but you can group the seconds into larger units for convenience. For example, the CAST( '1 week' AS INTERVAL ) is equal to CAST( '604800 seconds' AS INTERVAL ), which is equal to CAST( '7 days' AS INTERVAL ) you can use whichever format you find easiest to work with.

Table 2.9 lists the size and range for each of the temporal data types.

Table 2.9. Temporal Data Type Sizes and Ranges
Data Type	Size (in bytes)	Range
DATE	4	-01-MAR-4801 BC 31-DEC-32767
TIME [ WITHOUT TIME ZONE ]	4	-00:00:00.00 23:59:59.99
TIME WITH TIME ZONE	12	-00:00:00.00+12 23:59:59.00-12
TIMESTAMP [ WITH TIME ZONE ]	8	-24-NOV-4714 BC 31-DEC- 5874897
TIMESTAMP WITHOUT TIME ZONE	8	-24-NOV-4714 BC 31-DEC- 5874897
INTERVAL	12	--178000000 YEARS +178000000 YEARS

The data types that contain a time value (TIME, TIME WITH TIME ZONE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and INTERVAL) have microsecond precision. The DATE data type has a precision of one day.

Syntax for Literal Values

I covered date literal syntax pretty thoroughly in Chapter 1; see the section titled "Working with Date Values."

You may recall from Chapter 1 that date values can be entered in many formats, and you have to tell PostgreSQL how to interpret ambiguous values. Fortunately, the syntax for TIME, TIMESTAMP, and INTERVAL values is much more straightforward.

A TIME value stores hours, minutes, seconds, and microseconds. The syntax for a TIME literal is

hh:mm[:ss[.µ ]][AM|PM]µ

where hh specifies the hour, mm specifies the number of minutes past the hour, ss specifies the number of seconds, and µ specifies the number of microseconds. If you include an AM or PM indicator, the hh component must be less than or equal to 12; otherwise, the hour can range from 0 to 24.

Entering a TIME WITH TIME ZONE value is a bit more complex. A TIME WITH TIME ZONE value is a TIME value, plus a time zone. The time zone component can be specified in two ways. First, you can include an offset (in minutes and hours) from UTC:

hh:mm[:ss[.µ ]][AM|PM][{+|-}HH[:MM]]

where HH is the number of hours and MM is the number of minutes distant from UTC. Negative values are considered to be west of the prime meridian, and positive values are east of the prime meridian.

You can also use a standard time zone abbreviation (such as UTC, PDT, or EST) to specify the time zone:

hh:mm[:ss[.µ ]][AM|PM][ZZZ]

Table 2.10 shows all the time zone abbreviations accepted by PostgreSQL version 8.0.

Table 2.10. PostgreSQL Time Zone Names
Names	Offset	Description
`FJST`	-13:00	Fiji Summer Time
`FJT`	-12:00	Fiji Time
`IDLW`	-12:00	International Date Line, West
`BST`	-11:00	Bering Summer Time
`NT`	-11:00	Nome Time
`NUT`	-11:00	Niue Time
`AHST`	-10:00	Alaska-Hawaii Std Time
`CAT`	-10:00	Central Alaska Time
`HST`	-10:00	Hawaii Std Time
`THAT`	-10:00	Tahiti Time
`TKT`	-10:00	Tokelau Time
`MART`	-09:30	Marquesas Time
`AKST`	-09:00	Alaska Standard Time
`GAMT`	-09:00	Gambier Time
`HDT`	-09:00	Hawaii/Alaska Daylight Time
`YST`	-09:00	Yukon Standard Time
`AKDT`	-08:00	Alaska Daylight Time
`PST`	-08:00	Pacific Standard Time
`YDT`	-08:00	Yukon Daylight Time
`MST`	-07:00	Mountain Standard Time
`PDT`	-07:00	Pacific Daylight Time
`CST`	-06:00	Central Standard Time
`EAST`	-06:00	Easter Island Time
`GALT`	-06:00	Galapagos Time
`MDT`	-06:00	Mountain Daylight Time
`ZP6`	-06:00	UTC +6 hours
`ACT`	-05:00	Atlantic/Porto Acre Time
`CDT`	-05:00	Central Daylight Time
`COT`	-05:00	Columbia Time
`EASST`	-05:00	Easter Island Summer Time
`ECT`	-05:00	Ecuador Time
`EST`	-05:00	Eastern Standard Time
`PET`	-05:00	Peru Time
`ZP5`	-05:00	UTC +5 hours
`ACST`	-04:00	Atlantic/Porto Acre Summer Time
`AMT`	-04:00	Amazon Time (Porto Velho)
`AST`	-04:00	Atlantic Std Time (Canada)
`BOT`	-04:00	Bolivia Time
`CLT`	-04:00	Chile Time
`ECT`	-04:00	Eastern Caribbean Time
`EDT`	-04:00	Eastern Daylight Time
`GYT`	-04:00	Guyana Time
`PYT`	-04:00	Paraguay Time
`VET`	-04:00	Venezuela Time
`ZP4`	-04:00	UTC +4 hours
`NFT`	-03:30	Newfoundland Standard Time
`NST`	-03:30	Newfoundland Standard Time
`ADT`	-03:00	Atlantic Daylight Time
`AMST`	-03:00	Amazon Summer Time (Porto Velho)
`ART`	-03:00	Argentina Time
`AWT`	-03:00	Brazil Time
`BRT`	-03:00	Brasilia Time
`BST`	-03:00	Brazil Standard Time
`CLST`	-03:00	Chile Summer Time
`FKST`	-03:00	Falkland Islands Summer Time
`GFT`	-03:00	French Guiana Time
`PYST`	-03:00	Paraguay Summer Time
`UYT`	-03:00	Uruguay Time
`WGT`	-03:00	West Greenland Time
`NDT`	-02:30	Newfoundland Daylight Time
`BRST`	-02:00	Brasilia Summer Time
`FKT`	-02:00	Falkland Islands Time
`FNT`	-02:00	Fernando de Noronha Time
`PMDT`	-02:00	Pierre & Miquelon Daylight Time
`UYST`	-02:00	Uruguay Summer Time
`WGST`	-02:00	West Greenland Summer Time
`AZOT`	-01:00	Azores Time
`EGT`	-01:00	East Greenland Time
`FNST`	-01:00	Fernando de Noronha Summer Time
`SET`	-01:00	Seychelles Time
`WAT`	-01:00	West Africa Time
`AZOST`	+00:00	Azores Summer Time
`EGST`	+00:00	East Greenland Summer Time
`GMT`	+00:00	Greenwich Mean Time
`UTC`	+00:00	Universal Coordinated Time
`UT`	+00:00	Universal Time
`WET`	+00:00	Western Europe
`ZULU`	+00:00	Universal Time
`Z`	+00:00	ISO-8601 Universal Time
`BST`	+01:00	British Summer Time
`CET`	+01:00	Central European Time
`DNT`	+01:00	Dansk Normal Time
`FST`	+01:00	French Summer Time
`MET`	+01:00	Middle Europe Time
`MEWT`	+01:00	Middle Europe Winter Time
`MEZ`	+01:00	Middle Europe Zone
`NOR`	+01:00	Norway Standard Time
`SWT`	+01:00	Swedish Winter Time
`WEST`	+01:00	Western Europe Summer Time
`WEtdST`	+01:00	Western Europe Daylight Savings Time
`BDST`	+02:00	British Double Summer Time
`CEST`	+02:00	Central European Dayl.Time
`CETDST`	+02:00	Central European Dayl.Time
`EET`	+02:00	Eastern Europe, USSR Zone 1
`FWT`	+02:00	French Winter Time
`IST`	+02:00	Israel Time
`MEST`	+02:00	Middle Europe Summer Time
`MEtdST`	+02:00	Middle Europe Daylight Time
`SST`	+02:00	Swedish Summer Time
`BT`	+03:00	Baghdad Time
`EAT`	+03:00	East Africa Time
`EAT`	+03:00	Indian Antananarivo Time
`EEST`	+03:00	Eastern Europe Summer Time
`EEtdST`	+03:00	Eastern Europe Daylight Time
`HMT`	+03:00	Hellas Mediterranean Time
`MSK`	+03:00	Moscow Time
`IRT`	+03:30	Iran Time
`IT`	+03:30	Iran Time
`AMT`	+04:00	Armenia Time (Yerevan)
`AZT`	+04:00	Azerbaijan Time
`EAST`	+04:00	Indian Antananarivo Savings Time
`GET`	+04:00	Georgia Time
`MSD`	+04:00	Moscow Summer Time
`MUT`	+04:00	Mauritius Island Time
`RET`	+04:00	Reunion Island Time
`SCT`	+04:00	Mahe Island Time
`AFT`	+04:30	Kabul Time
`AMST`	+05:00	Armenia Summer Time (Yerevan)
`AZST`	+05:00	Azerbaijan Summer Time
`GEST`	+05:00	Georgia Summer Time
`IOT`	+05:00	Indian Chagos Time
`KGT`	+05:00	Kyrgyzstan Time
`MVT`	+05:00	Maldives Island Time
`PKT`	+05:00	Pakistan Time
`TFT`	+05:00	Kerguelen Time
`TJT`	+05:00	Tajikistan Time
`TMT`	+05:00	Turkmenistan Time
`UZT`	+05:00	Uzbekistan Time
`YEKT`	+05:00	Yekaterinburg Time
`NPT`	+05:45	Nepal Standard Time
`ALMT`	+06:00	Almaty Time
`BDT`	+06:00	Dacca Time
`BTT`	+06:00	Bhutan Time
`DUSST`	+06:00	Dushanbe Summer Time
`KGST`	+06:00	Kyrgyzstan Summer Time
`LKT`	+06:00	Lanka Time
`MAWT`	+06:00	Mawson, Antarctica
`NOVT`	+06:00	Novosibirsk Standard Time
`OMST`	+06:00	Omsk Time
`UZST`	+06:00	Uzbekistan Summer Time
`YEKST`	+06:00	Yekaterinburg Summer Time
`CCT`	+06:30	Indian Cocos (Island) Time
`MMT`	+06:30	Myanmar Time
`ALMST`	+07:00	Almaty Savings Time
`CVT`	+07:00	Christmas Island Time (Indian Ocean)
`CXT`	+07:00	Christmas Island Time (Indian Ocean)
`DAVT`	+07:00	Davis Time (Antarctica)
`ICT`	+07:00	Indochina Time
`JAVT`	+07:00	Java Time
`KRAST`	+07:00	Krasnoyarsk Summer Time
`NOVST`	+07:00	Novosibirsk Summer Time
`OMSST`	+07:00	Omsk Summer Time
`WAST`	+07:00	West Australian Std Time
`JT`	+07:30	Java Time
`AWST`	+08:00	Western Australia
`BNT`	+08:00	Brunei Darussalam Time
`BORT`	+08:00	Borneo Time (Indonesia)
`CCT`	+08:00	China Coast Time
`HKT`	+08:00	Hong Kong Time
`IRKT`	+08:00	Irkutsk Time
`KRAT`	+08:00	Krasnoyarsk Standard Time
`MYT`	+08:00	Malaysia Time
`PHT`	+08:00	Phillipine Time
`ULAT`	+08:00	Ulan Bator Time
`WADT`	+08:00	West Australian DST
`WST`	+08:00	West Australian Standard Time
`MT`	+08:30	Moluccas Time
`AWSST`	+09:00	Western Australia Time
`IRKST`	+09:00	Irkutsk Summer Time
`JAYT`	+09:00	Jayapura Time (Indonesia)
`JST`	+09:00	Japan Std Time, USSR Zone 8
`KST`	+09:00	Korea Standard Time
`PWT`	+09:00	Palau Time
`ULAST`	+09:00	Ulan Bator Summer Time
`WDT`	+09:00	West Australian DST
`YAKT`	+09:00	Yakutsk Time
`ACST`	+09:30	Central Australia
`CAST`	+09:30	Central Australian ST
`SAST`	+09:30	South Australian Std Time
`SAT`	+09:30	South Australian Std Time
`AEST`	+10:00	Australia Eastern Std Time
`DDUT`	+10:00	Dumont-d'Urville Time (Antarctica)
`EAST`	+10:00	East Australian Std Time
`EST`	+10:00	Australia Eastern Std Time
`GST`	+10:00	Guam Std Time, USSR Zone 9
`KDT`	+10:00	Korea Daylight Time
`LIGT`	+10:00	From Melbourne, Australia
`MPT`	+10:00	North Mariana Islands Time
`PGT`	+10:00	Papua New Guinea Time
`trUK`	+10:00	Truk Time
`VLAT`	+10:00	Vladivostok Time
`YAKST`	+10:00	Yakutsk Summer Time
`YAPT`	+10:00	Yap Time (Micronesia)
`ACSST`	+10:30	Central Australia Time
`CADT`	+10:30	Central Australian DST
`CST`	+10:30	Australia Central Std Time
`LHST`	+10:30	Lord Howe Standard Time, Australia
`SADT`	+10:30	South Australian Daylight Time
`AESST`	+11:00	Eastern Australia
`LHDT`	+11:00	Lord Howe Daylight Time, Australia
`MAGT`	+11:00	Magadan Time
`NCT`	+11:00	New Caledonia Time
`PONT`	+11:00	Ponape Time (Micronesia)
`VLAST`	+11:00	Vladivostok Summer Time
`VUT`	+11:00	Vanuata Time
`ANAT`	+12:00	Anadyr Time (Russia)
`CKT`	+12:00	Cook Islands Time
`GILT`	+12:00	Gilbert Islands Time
`IDLE`	+12:00	International Date Line, East
`KOST`	+12:00	Kosrae Time
`MAGST`	+12:00	Magadan Summer Time
`MHT`	+12:00	Kwajalein Time
`NZST`	+12:00	New Zealand Standard Time
`NZT`	+12:00	New Zealand Time
`PETT`	+12:00	Petropavlovsk-Kamchatski Time
`TVT`	+12:00	Tuvalu Time
`WAKT`	+12:00	Wake Time
`WFT`	+12:00	Wallis and Futuna Time
`CHAST`	+12:45	Chatham Island Time
`ANAST`	+13:00	Anadyr Summer Time (Russia)
`NZDT`	+13:00	New Zealand Daylight Time
`PETST`	+13:00	Petropavlovsk-Kamchatski Summer Time
`PHOT`	+13:00	Phoenix Islands (Kiribati) Time
`TOT`	+13:00	Tonga Time
`CHADT`	+13:45	Chatham Island Daylight Time
`LINT`	+14:00	Line Islands Time (Kiribati)

I mentioned earlier in this section that an INTERVAL value represents a time span. I also mentioned than an INTERVAL stores some number of seconds. The syntax for an INTERVAL literal allows you to specify the number of seconds in a variety of units.

The format of an INTERVAL value is

quantity unit [quantity unit ...][AGO]

The unit component specifies a number of seconds, as shown in Table 2.11. The quantity component acts as a multiplier (and may be fractional). If you have multiple quantity unit groups, they are all added together. The optional phrase AGO will cause the INTERVAL to be negative.

Table 2.11. `INTERVAL` Units
Description	Seconds	Unit Names
Microsecond[3]	.000001	`us, usec, usecs, useconds, microsecon`
Millisecond[3]	.001	-`ms, msecs, mseconds, millisecon`
Second	1	`s, sec, secs, second, seconds`
Minute	60	`m, min, mins, minute, minutes`
Hour	3600	`h, hr, hrs, hours`
Day	86400	`d, day, days`
Week	604800	`w, week, weeks`
Month (30 days)	2592000	`mon, mons, month, months`
Year	31557600	`y, yr, yrs, year, years`
Decade	315576000	`dec, decs, decade, decades`
Century	3155760000	`c, cent, century, centuries`
Millennium	31557600000	`mil, mils, millennia, millennium`

[3] Millisecond and microsecond can be used only in combination with another date/time component. For example, CAST( '1 SECOND 5000 MSEC' AS INTERVAL ) results in an interval of six seconds.

You can use the EXTRACT( EPOCH FROM interval ) function to convert an INTERVAL into a number of seconds. A few sample INTERVAL values are shown in Table 2.12. The Display column shows how PostgreSQL would format the Input Value for display. The EPOCH column shows the value that would be returned by extracting the EPOCH from the Input Value.

Table 2.12. Sample `INTERVAL` Values
Input Value	Display	EPOCH
`.5 minutes`	`00:00:30`	30
`22 seconds 1 msec`	`00:00:22.00`	22.001
`22.001 seconds`	`00:00:22.00`	22.001
`10 centuries 2 decades`	`1020 years`	32188752000
`1 week 2 days 3.5 msec`	`9 days 00:00:00.00`	777600.0035

Supported Operators

There are two types of operators that you can use with temporal values: arithmetic operators (addition and subtraction) and comparison operators.

You can add an INT4, a TIME, or a TIMETZ to a DATE. When you add an INT4, you are adding a number of days. Adding a TIME or TIMETZ to a DATE results in a TIMESTAMP. Table 2.13 lists the valid data type and operator combinations for temporal data types. The last column in Table 2.14 shows the data type of the resulting value.

Table 2.13. Arithmetic Date/Time Operators
Data Types	Valid Operators (q)	Result Type
`DATE` q `DATE`	`-`	`INTEGER`
`DATE` q `TIME`	`+`	`TIMESTAMP`
`DATE` q `TIMETZ`	`+`	`TIMESTAMP WITH TIMEZONE`
`DATE` q `INT4`	`+ -`	`DATE`
`TIME` q `DATE`	`+`	`TIMESTAMP`
`TIME` q `INTERVAL`	`+ -`	`TIME`
`TIMETZ` q `DATE`	`+`	`TIMESTAMP WITH TIMEZONE`
`TIMETZ` q `INTERVAL`	`+ -`	`TIMETZ`
`TIMESTAMP` q `TIMESTAMP`	`-`	`INTERVAL`
`TIMESTAMP` q `INTERVAL`	`+ -`	`TIMESTAMP WITH TIMEZONE`
`INTERVAL` q `TIME`	`+`	`TIME WITHOUT TIMEZONE`

Table 2.14. Arithmetic Date/Time Operator Examples
Example	Result
`'23-JAN-2003'::DATE` - `'23-JAN-2002'::DATE`	`365`
`'23-JAN-2003'::DATE + '2:35 PM'::TIME`	`2003-01-23 14:35:00`
`'23-JAN-2003'::DATE + '2:35 PM GMT'::TIMETZ`	`2003-01-23 09:35:00-05`
`'23-JAN-2003'::DATE + 2::INT4`	`2003-01-25`
`'2:35 PM'::TIME + '23-JAN-2003'::DATE`	`2003-01-23 14:35:00`
`'2:35 PM'::TIME + '2 hours 5 minutes'::INTERVAL`	`16:40:00`
`'2:35 PM EST'::TIMETZ + '23-JAN-2003'::DATE`	`2003-01-23 14:35:00-05`
`'2:35 PM EST'::TIMETZ + '2 hours 5 minutes'::INTERVAL`	`16:40:00-05`
`'23-JAN-2003 2:35 PM EST'::TIMESTAMP - '23-JAN-2002 1:00 PM EST'::TIMESTAMP`	`365 days 01:35`
`'23-JAN-2003 2:35 PM EST'::TIMESTAMP + '3 days 2 hours 5 minutes'::INTERVAL`	`2003-01-26 16:40:00-05`
`'2 hours 5 minutes'::INTERVAL + '2:34 PM'::TIME`	`16:39:00`

Table 2.14 shows how each of the arithmetic operators behave when applied to date/time values.

Using the temporal comparison operators, you can determine the relationship between two date/time values. For purposes of comparison, an earlier date/time value is considered to be less than a later date/time value.

Table 2.15 shows how you can combine the various temporal types with comparison operators.

Table 2.15. Date/Time Comparison Operators
Data Types	Valid Operators (q)
`date` q `date`	`< <= <> = >= >`
`time` q `time`	`< <= <> = >= >`
`timetz` q `timetz`	`< <= <> = >= >`
`timestamp` q `timestamp`	`< <= <> = >= >`

Date Time Values

Категории