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: README, Cargo.toml version 0.1.9, META.json

pg-when parses a constrained natural-language time expression and returns either a PostgreSQL timestamp with time zone or Unix epoch values at different resolutions.

CREATE EXTENSION pg_when;

SELECT when_is('next friday at 8:00 pm in America/New_York');
SELECT seconds_at('next friday at 8:00 pm in America/New_York');
SELECT millis_at('next friday at 8:00 pm in America/New_York');
SELECT micros_at('next friday at 8:00 pm in America/New_York');
SELECT nanos_at('next friday at 8:00 pm in America/New_York');

Supported Query Shape

The parser accepts up to three parts:

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

If no timezone is provided, upstream says the default is UTC.

Common Inputs

• relative dates: today, tomorrow, last month, this friday, 5 days ago, in 2 years
• exact dates: YYYY-MM-DD, DD/MM/YYYY, January 10, 2004, 10 Jan 2004
• relative times: noon, midnight, morning, evening, next hour
• exact times: 8:30 pm, 15:45
• time zones: America/New_York, Europe/London, UTC-08:00, UTC+05:30

Examples

SELECT when_is('5 days ago at this hour in Asia/Tokyo');
SELECT when_is('in 2 months at midnight in UTC-8');
SELECT when_is('December 31, 2026 at evening');

Caveats

• The extension is aimed at the documented grammar above, not arbitrary English.
• Upstream still lists source/runtime support and Docker image examples from PostgreSQL 13 through 18, but this repo’s package matrix is PostgreSQL 14 through 18 only; do not assume Pigsty packages for PostgreSQL 13.
• Upstream Cargo.toml currently pins pgrx 0.15.0; this repo’s package metadata records a manual pgrx 0.17.0 upgrade.