pg_when

pg_when : Natural language time parsing for PostgreSQL

Overview

Packages

Source

pig build pkg pg_when;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install pg_when;		# install via package name, for the active PG version

pig install pg_when -v 18;   # install for PG 18
pig install pg_when -v 17;   # install for PG 17
pig install pg_when -v 16;   # install for PG 16
pig install pg_when -v 15;   # install for PG 15
pig install pg_when -v 14;   # install for PG 14

Create this extension with:

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.