Skip to main content

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.