pg_when

Overview

manually upgraded PGRX from 0.15.0 to 0.17.0 by Vonng

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:

• Install
• pig
• dnf
• apt

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: official README, official repo

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 distributes ready-made Docker images for PostgreSQL 13 through 18, but the stub should focus on SQL usage rather than container setup.