ISFINITE

Test whether a date, timestamp, or interval value is finite (not positive or negative infinity).

Category: datetimeReturns: BOOLEANDialect: PostgreSql

Syntax

ISFINITE(value)

Description

## Overview Returns TRUE for finite date, timestamp, or interval values and FALSE for the special sentinel values 'infinity' and '-infinity'. These sentinels represent 'later than any real date' and 'earlier than any real date' respectively and are used to model open-ended ranges. Use ISFINITE to filter out sentinel values before arithmetic or external export, since infinity propagates through most operations but cannot be written to systems that do not support it. ## Behavior - Returns a BOOLEAN. - Returns NULL if the argument is NULL. - Returns FALSE only for the literal values 'infinity' and '-infinity' on DATE and TIMESTAMP types. - INTERVAL values currently do not support infinity; ISFINITE(interval) returns TRUE for any concrete interval, NULL for NULL input. - Works on TIMESTAMP, TIMESTAMP WITH TIME ZONE, and DATE inputs. ## Compatibility - Matches the ISFINITE convention for date-family sentinel handling.

Parameters

Name	Type	Description
`value`		Specifies the date, timestamp, or interval value to test for finiteness.

Examples

-- Normal date is finite
SELECT ISFINITE(DATE '2025-03-15') AS is_finite;

-- Positive infinity returns FALSE
SELECT ISFINITE(TIMESTAMP 'infinity') AS is_finite;

-- Negative infinity returns FALSE
SELECT ISFINITE(TIMESTAMP '-infinity') AS is_finite;

-- Filter out open-ended event ranges
SELECT event_id, event_ts
FROM analytics.events.user_activity
WHERE ISFINITE(event_ts);

-- Count infinite sentinel values in a staging table
SELECT COUNT(*) FILTER (WHERE NOT ISFINITE(close_date)) AS open_ended_rows
FROM commerce.contracts.enterprise_deals;

-- Combine with COALESCE to replace infinities with a cap date
SELECT contract_id,
       CASE WHEN ISFINITE(close_date) THEN close_date ELSE DATE '9999-12-31' END AS capped_close
FROM commerce.contracts.enterprise_deals;

Pitfalls

Infinity sentinels propagate through arithmetic: infinity + INTERVAL '1 day' remains infinity. Applications that perform downstream math must check ISFINITE first.
External systems (CSV exports, BI tools, Parquet readers) rarely understand the infinity sentinel. Convert to a concrete cap date (for example 9999-12-31) before export.
NULL and infinity are different: NULL represents missing data while infinity represents 'open-ended'. ISFINITE distinguishes both, but SQL NULL semantics (NULL propagation) can still hide infinity bugs downstream.