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.
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 +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.
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 7.1.3.
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.
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.
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 |
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.
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.
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.
Data Types |
Valid Operators (q) |
---|---|
date q date |
< <= <> = >= > |
time q time |
< <= <> = >= > |
timetz q timetz |
< <= <> = >= > |
timestamp q timestamp |
< <= <> = >= > |