Table 9.32 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 9.31 illustrates the behaviors of the basic arithmetic operators (+, *, etc.). For formatting functions, refer to Section 9.8. You should be familiar with the background information on date/time data types from Section 8.5.
In addition, the usual comparison operators shown in Table 9.1 are available for the date/time types. Dates and timestamps (with or without time zone) are all comparable, while times (with or without time zone) and intervals can only be compared to other values of the same data type. When comparing a timestamp without time zone to a timestamp with time zone, the former value is assumed to be given in the time zone specified by the TimeZone configuration parameter, and is rotated to UTC for comparison to the latter value (which is already in UTC internally). Similarly, a date value is assumed to represent midnight in the TimeZone zone when comparing it to a timestamp.
All the functions and operators described below that take time or timestamp inputs actually come in two variants: one that takes time with time zone or timestamp with time zone, and one that takes time without time zone or timestamp without time zone. For brevity, these variants are not shown separately. Also, the + and * operators come in commutative pairs (for example both date + integer and integer + date); we show only one of each such pair.
Table 9.31. Date/Time Operators
|
Operator Description Example(s) |
|---|
|
Add a number of days to a date
|
|
Add an interval to a date
|
|
Add a time-of-day to a date
|
|
Add intervals
|
|
Add an interval to a timestamp
|
|
Add an interval to a time
|
|
Negate an interval
|
|
Subtract dates, producing the number of days elapsed
|
|
Subtract a number of days from a date
|
|
Subtract an interval from a date
|
|
Subtract times
|
|
Subtract an interval from a time
|
|
|