DateTime 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 zone ”if 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-JAN-4713 BC 31-DEC-32767 AD
TIME [ WITHOUT TIME ZONE ]	4	00:00:00.00 23:59:59.99
TIME WITH TIME ZONE	4	00:00:00.00+12 23:59:59.00-12
TIMESTAMP [ WITH TIME ZONE ]	8	14-DEC-1901 18-JAN-2038
TIMESTAMP WITHOUT TIME ZONE	8	14-DEC-1901 18-JAN-2038
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  [. ]][AMPM] 

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  [. ]][AMPM][{+-}  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  [. ]][AMPM][  ZZZ  ] 

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

Table 2.10. PostgreSQL Time Zone Names

Names	Offset	Description
IDLW	“12:00	International Date Line West
NT	“11:00	Nome Time
AHST		Alaska/Hawaii Standard Time
CAT	“10:00	Central Alaska Time
HST		Hawaii Standard Time
YST		Yukon Standard Time
HDT	“09:00	Alaska/Hawaii Daylight Time
AKST		Alaska Standard Time
YDT		Yukon Daylight Time
PST	“08:00	Pacific Standard Time
AKDT		Alaska Daylight Time
MST		Mountain Standard Time
PDT	“07:00	Pacific Daylight Time
CST	“06:00	Central Standard Time
MDT		Mountain Daylight Time
EST		Eastern Standard Time
CDT	“05:00	Central Daylight Time
ACT		Atlantic/Porto Acre Standard Time
AST		Atlantic Standard Time (Canada)
EDT	“04:00	Eastern Daylight Time
ACST		Atlantic/Porto Acre Summer Time
NFT, NST	“03:30	Newfoundland Standard Time
ADT	“03:00	Atlantic Daylight Time
AWT		Atlantic War Time
NDT	“02:30	Newfoundland Daylight Time
SET	“01:00	Seychelles Time
WAT		West Africa Time
GMT		Greenwich Mean Time
UCT		Universal Time Coordinated
UT	+00:00	Universal Time
WET		Western Europe Time
ZULU, Z		Zulu
BST		British Summer Time
CET		Central European Time
DNT		Dansk Normal Time
FST		French Summer Time
MET	+01:00	Middle Europe Time
MEWT		Middle Europe Winter Time
MEZ		Middle Europe Zone
NOR		Norway Standard Time
WETDST		Western Europe Daylight Savings Time
SWT		Swedish Winter Time
EET		Eastern Europe (USSR Zone 1)
IST		Israel
SST		Swedish Summer Time
METDST		Middle Europe Daylight Time
MEST	+02:00	Middle Europe Summer Time
FWT		French Winter Time
CETDST		Central European Daylight Savings Time
CEST		Central European Savings Time
BDST		British Double Standard Time
BT		Baghdad Time
HMT	+03:00	Hellas Mediterranean Time
EETDST		Eastern Europe Daylight Savings Time
IT	+03:30	Iran Time
JT	+07:30	Java Time
WAST	+07:00	West Australian Standard Time
AWST		West Australian Standard Time
CCT	+08:00	China Coast Time
WST		West Australian Standard Time
WADT		West Australian Daylight Time
MT	+08:30	Moluccas Time
JST		Japan Standard Time(USSR Zone 8)
KST	+09:00	Korea Standard Time
WDT		West Australian Daylight Time
AWSST		Australia Western Summer Standard Time
ACST		Australia Central Standard Time
CAST	+09:30	Australia Central Standard Time
SAST		South Australian Standard Time
AEST		Australia Eastern Standard Time
EAST	+10:00	Australia Eastern Standard Time
GST		Guam Standard Time (USSR Zone 9)
LIGT		Melbourne
SADT	+10:30	South Australian Daylight Time
CADT		Central Australia Daylight Savings Time
ACSST		Central Australia Summer Standard Time
AESST	+11:00	Australia Eastern Summer Standard Time
IDLE		International Date Line East
NZST	+12:00	New Zealand Standard Time
NZT		New Zealand Time
NZDT	+13:00	New Zealand Daylight Time

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, microsecond
Millisecond [3]	.001	ms, msecs, mseconds, millisecon, millisecond
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 shows how each of the arithmetic operators behave when applied to date/time values.

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

Using the temporal comparison operators, you can determine the relationship between to 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	< <= <> = >= >