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: README and project repo.

pg-when is a PostgreSQL extension for creating time values from natural-language phrases. It exposes the same parsed expression through multiple return formats: when_is, seconds_at, millis_at, micros_at, and nanos_at.

The query syntax combines 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, the extension defaults to UTC.

Supported Components

<date> can be relative or exact.

Relative date examples from the README include:

• today
• yesterday
• tomorrow
• next week
• last month
• this friday
• 5 days ago
• in 2 years

Exact dates can be written as:

• YYYY-MM-DD or YYYY/MM/DD
• DD-MM-YYYY or DD/MM/YYYY
• Month D, YYYY
• D Month YYYY

<time> can also be relative or exact.

Relative time examples include:

• noon
• midnight
• morning
• evening
• next hour
• previous minute
• this hour

Exact times can be written in 12-hour or 24-hour forms such as 8:30 pm or 15:45.

Timezones accept either IANA names or UTC offsets, for example America/New_York or UTC-08:00.

Examples

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

Deployment

The upstream README shows Docker images for PostgreSQL 13 through 18. That matches the package metadata in this repository.