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.