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.

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.

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.

^[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.

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.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.