pg_when

Overview

Upstream 0.1.10 supports PostgreSQL 13-18 and pins pgrx 0.18.1; PIGSTY packages PostgreSQL 14-18 with a locked pgrx 0.19.1 compatibility update.

Version

Build

You can build the RPM / DEB packages for pg_when using pig build:

pig build pkg pg_when         # build RPM / DEB packages

Install

You can install pg_when directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_when;          # Install for current active PG version

pig ext install -y pg_when -v 18  # PG 18
pig ext install -y pg_when -v 17  # PG 17
pig ext install -y pg_when -v 16  # PG 16
pig ext install -y pg_when -v 15  # PG 15
pig ext install -y pg_when -v 14  # PG 14

dnf install -y pg_when_18       # PG 18
dnf install -y pg_when_17       # PG 17
dnf install -y pg_when_16       # PG 16
dnf install -y pg_when_15       # PG 15
dnf install -y pg_when_14       # PG 14

apt install -y postgresql-18-pg-when   # PG 18
apt install -y postgresql-17-pg-when   # PG 17
apt install -y postgresql-16-pg-when   # PG 16
apt install -y postgresql-15-pg-when   # PG 15
apt install -y postgresql-14-pg-when   # PG 14

Create Extension:

CREATE EXTENSION pg_when;

Usage

Sources:

• pg_when 0.1.10 on PGXN
• pg_when 0.1.10 README
• pg_when 0.1.10 Cargo manifest
• pg_when 0.1.10 control file
• pg_when 0.1.10 exported functions
• pg_when 0.1.10 relative-date implementation

pg_when 0.1.10 parses a constrained natural-language date and time expression and returns either a PostgreSQL timestamptz value or a Unix epoch value at a selected precision.

CREATE EXTENSION pg_when;

SELECT when_is('next friday at 8:00 pm in America/New_York');
SELECT seconds_at('5 days ago at this hour in Asia/Tokyo');
SELECT millis_at('in 2 months at midnight in UTC-8');
SELECT micros_at('December 31, 2026 at evening');
SELECT nanos_at('last monday at 22:30');

Query Shape

A query can contain a date, a time, and a time zone, joined by at and in:

SELECT when_is('<date> at <time> in <timezone>');
SELECT when_is('<date>');
SELECT when_is('<date> in <timezone>');
SELECT when_is('<time>');
SELECT when_is('<time> in <timezone>');
SELECT when_is('<date> at <time>');

If the time zone is omitted, the parser uses UTC. Supported inputs include relative dates such as tomorrow, last month, and 5 days ago; exact dates in common numeric and month-name forms; relative times such as noon, midnight, and next hour; clock times; IANA time-zone names; and UTC offsets.

Function Index

• when_is(text) returns timestamptz.
• seconds_at(text) returns Unix epoch seconds.
• millis_at(text) returns Unix epoch milliseconds.
• micros_at(text) returns Unix epoch microseconds.
• nanos_at(text) returns Unix epoch nanoseconds.

Compatibility and Boundaries

• The parser implements the documented grammar; it is not a general-purpose natural-language interpreter.
• Upstream 0.1.10 declares PostgreSQL 13–18 features and pins pgrx 0.18.1. Pigsty packages cover PostgreSQL 14–18 and apply a locked pgrx 0.19.1 compatibility update.
• pg_when is not relocatable and its control file requires a superuser for CREATE EXTENSION.
• Invalid text raises an error. All five functions are STRICT, so a null input returns null; nanos_at(text) also errors when the epoch nanoseconds cannot fit in bigint.
• The 0.1.10 SQL functions are declared IMMUTABLE, but relative expressions such as now, tomorrow, and 5 days ago read the wall clock. Do not use relative-input calls in expression indexes or generated columns, and do not rely on them being reevaluated in cached plans; only fully specified date, time, and time-zone inputs are time-independent.