In PostgreSQL, the isfinite() function tests for a finite date, timestamp or interval.

This can be useful, because Postgres supports infinite dates/timestamps. For example, you could have a timestamp of infinity or negative infinity, and this function allows you to test for that.

Syntax

The function takes one parameter, which can be either a date, timestamp, or interval:

isfinite(date)
isfinite(timestamp)
isfinite(interval)

Example

Here’s an example of how it works with a date value.

SELECT isfinite(date '2020-10-23');

Result:

True

Depending on where you run it, you might get either a true or false, or a t or f result.

I got the above result when using Azure Data Studio.

When I run it in psql, I get the following result:

t

Timestamp

Here it is with a timestamp value.

SELECT isfinite(timestamp '2020-10-23 12:30:45');

Result:

True

Interval

Here it is with an interval value.

SELECT isfinite(interval '2 hours 30 minutes');

Result:

True

Infinity

All the previous examples return true. Here’s one that returns false. In this case I use the infinity constant.

SELECT isfinite('infinity'::timestamp);

Result:

False

Negative Infinity

Same result when using negative infinity.

SELECT isfinite('-infinity'::timestamp);

Result:

False