Mathematical Functions and Operators
Description
Mathematical operators are provided for many PostgreSQL types. For types without standard mathematical conventions (e.g., date/time types) we describe the actual behavior in subsequent sections.
Mathematical Operators
|
Operator |
Description |
Example |
Result |
|
+ |
addition |
2 + 3 |
5 |
|
– |
subtraction |
2 – 3 |
-1 |
|
* |
multiplication |
2 * 3 |
6 |
|
/ |
division (integer division truncates the result) |
4 / 2 |
2 |
|
% |
modulo (remainder) |
5 % 4 |
1 |
|
^ |
exponentiation (associates left to right) |
2.0 ^ 3.0 |
8 |
|
|/ |
square root | |/ 25.0 | 5 |
|
||/ |
cube root | ||/ 27.0 | 3 |
|
! |
factorial | 5 ! | 120 |
|
!! |
factorial (prefix operator) | !! 5 | 120 |
|
@ |
absolute value | @ -5.0 | 5 |
| & |
bitwise AND |
91 & 15 | 11 |
|
| |
bitwise OR | 32 | 3 | 35 |
|
# |
bitwise XOR | 17 # 5 | 20 |
|
~ |
bitwise NOT | ~1 | -2 |
|
<< |
bitwise shift left | 1 << 4 | 16 |
|
>> |
bitwise shift right | 8 >> 2 | 2 |
Date/Time Functions and Operators
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.
|
Operator |
Example |
Result |
|
+ |
date ‘2001-09-28’ + integer ‘7 |
date ‘2001-10-05’ |
|
+ |
date ‘2001-09-28’ + interval ‘1 hour’ |
timestamp ‘2001-09-28 01:00:00’ |
|
+ |
date ‘2001-09-28′ + time ’03:00’ |
timestamp ‘2001-09-28 03:00:00’ |
|
+ |
interval ‘1 day’ + interval ‘1 hour’ |
interval ‘1 day 01:00:00’ |
|
+ |
timestamp ‘2001-09-28 01:00′ + interval ’23 hours’ |
timestamp ‘2001-09-29 00:00:00’ |
|
+ |
time ’01:00′ + interval ‘3 hours’ |
time ’04:00:00′ |
|
– |
– interval ’23 hours’ |
interval ‘-23:00:00’ |
|
– |
date ‘2001-10-01’ – date ‘2001-09-28’ |
integer ‘3’ (days) |
|
– |
date ‘2001-10-01’ – integer ‘7’ |
date ‘2001-09-24’ |
|
– |
date ‘2001-09-28’ – interval ‘1 hour’ |
timestamp ‘2001-09-27 23:00:00’ |
|
– |
time ’05:00′ – time ’03:00′ |
interval ’02:00:00′ |
|
– |
time ’05:00′ – interval ‘2 hours’ |
time ’03:00:00′ |
|
– |
timestamp ‘2001-09-28 23:00′ – interval ’23 hours’ |
timestamp ‘2001-09-28 00:00:00’ |
|
– |
interval ‘1 day’ – interval ‘1 hour’ |
interval ‘1 day -01:00:00’ |
|
– |
timestamp ‘2001-09-29 03:00’ – timestamp ‘2001-09-27 12:00’ |
interval ‘1 day 15:00:00’ |
|
* |
900 * interval ‘1 second’ |
interval ’00:15:00′< |
|
*< |
21 * interval ‘1 day’ |
interval ’21 days’ |
|
* |
double precision ‘3.5’ * interval ‘1 hour’ |
interval ’03:30:00′ |
|
/ |
interval ‘1 hour’ / double precision ‘1.5’ |
interval ’00:40:00′ |
Date/Time Functions
|
Function |
Return Type |
Description |
Example |
Result |
|
age(timestamp, timestamp) |
interval |
Subtract arguments, producing a “symbolic” result that uses years and months |
age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’) |
43 years 9 mons 27 days |
|
age(timestamp) |
interval |
Subtract from current_date (at midnight) |
age(timestamp ‘1957-06-13’) |
43 years 8 |
|
clock_timestamp ( ) |
timestamp with time zone |
Current data and time (changes during statement execution); see section 9.9.4 |
||
|
current_date |
date | Current date; see Section 9.9.4 | ||
|
current_time |
time with time zone | Current time of day; see Section 9.9.4 | ||
|
current_timestamp |
timestamp with time zone | Current date and time (start of current transaction); see Section 9.9.4 | ||
|
date_part(text, timestamp) |
double precision |
Get subfield (equivalent to extract); see Section 9.9.1 |
date_part(‘hour’, timestamp ‘2001-02-16 20:38:40’) |
20 |
|
date_part(text, in terval) |
double precision |
Get subfield (equivalent to extract); see Section 9.9.1 |
date_part(‘month’, interval ‘2 years 3 months’) |
3 |
|
date_trunc(text, timestamp) |
timestamp |
Truncate to specified precision; see also Section 9.9.2 |
date_trunc(‘hour’, timestamp ‘2001-02-16 20:38:40’) |
2001-02-16 20:00:00 |
|
extract(field from timestamp) |
double precision |
Get subfield; see Section 9.9.1 |
extract(hour from timestamp ‘2001-02-16 20:38:40’) |
20 |
|
extract(field from interval) |
double precision |
Get subfield; see Section 9.9.1 |
extract(month from interval ‘2 years 3 months’) |
3 |
|
isfinite(date) |
boolean |
Test for finite date (not +/-infinity) |
isfinite(date ‘2001-02- 16’) |
true |
|
isfinite(timestamp ) |
boolean |
Test for finite time stamp (not +/-infinity) |
isfinite(timestamp ‘2001-02-16 21:28:30’) |
true |
|
isfinite(interval) |
boolean |
Test for finite interval |
isfinite(interval ‘4 hours’) |
true |
|
justify_days( interval) |
interval |
Adjust interval so 30- day time periods are represented as months |
justify_days(interval ’35 days’) |
1 mon 5 days |
|
justify_hours( interval) |
interval |
Adjust interval so 24- hour time periods are represented as days |
justify_hours(interval ’27 hours’) |
1 day 03:00:00 |
|
justify_interval( interval) |
interval |
Adjust interval using justify_days and justify_hours, with additional sign adjustments |
justify_interval(interv al ‘1 mon -1 hour’) |
29 days 23:00:00 |
|
localtime |
time |
Current time of day; see Section 9.9.4 |
||
|
localtimestamp |
timestamp |
Current date and time (start of current transaction); see Section 9.9.4 |
||
|
now() |
timestamp with time zone |
Current date and time (start of current transaction); see Section 9.9.4 |
||
|
statement_timestam p() |
timestamp with time zone |
Current date and time (start of current statement); see Section 9.9.4 |
||
|
timeofday() |
text |
Current date and time (like clock_timestamp , but as a text string); see Section 9.9.4 |
||
|
transaction_timest amp() |
timestamp with time zone |
Current date and time (start of current transaction); see Section 9.9.4 |
||
|
transaction_timest amp() |
timestamp with time zone |
Current date and time (start of current transaction); see Section 9.9.4 |
In addition to these functions, the SQL OVERLAPS operator is supported:
(start1, end1) OVERLAPS (start2, end2) (start1, length1) OVERLAPS (start2, length2)
This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval. When a pair of values is provided, either the start or the end can be written first; OVERLAPS automatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.
SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Result: true SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Result: false SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS (DATE '2001-10-30', DATE '2001-10-31'); Result: false SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS (DATE '2001-10-30', DATE '2001-10-31');
Result: true
When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances (or decrements) the date of the timestamp with time zone by the indicated number of days. Across daylight saving time changes (with the session time zone set to a time zone that recognizes DST), this means interval ‘1 day’ does not necessarily equal interval ’24 hours’. For example, with the session time zone set to CST7CDT, timestamp with time zone ‘2005-04-02 12:00-07’ + interval ‘1 day’ will produce timestamp with time zone ‘2005-04-03 12:00-06’, while adding interval ’24 hours’ to the same initial timestamp with time zone produces timestamp with time zone ‘2005-04-03 13:00-06’, as there is a change in daylight saving time at 2005-04-03 02:00 in time zone CST7CDT.
Note there can be ambiguity in the months returned by age because different months have a different number of days. PostgreSQL’s approach uses the month from the earlier of the two dates when calculating partial months. For example, age(‘2004-06-01’, ‘2004-04-30’) uses April to yield 1 mon 1 day, while using May would yield 1 mon 2 days because May has 31 days, while April has only 30.