Date/Time Values

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 [phone] 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	< <= <> = >= >