pg-when

A PostgreSQL extension for creating time values with natural language.

SELECT when_is('5 days ago at this hour in Asia/Tokyo');
SELECT seconds_at('5 days ago at this hour in Asia/Tokyo');
SELECT millis_at('5 days ago at this hour in Asia/Tokyo');
SELECT micros_at('5 days ago at this hour in Asia/Tokyo');
SELECT nanos_at('5 days ago at this hour in Asia/Tokyo');

SELECT whenis('next friday at 8:00 pm in America/NewYork');
SELECT secondsat('next friday at 8:00 pm in America/NewYork');
SELECT millisat('next friday at 8:00 pm in America/NewYork');
SELECT microsat('next friday at 8:00 pm in America/NewYork');
SELECT nanosat('next friday at 8:00 pm in America/NewYork');

SELECT when_is('in 2 months at midnight in UTC-8');
SELECT seconds_at('in 2 months at midnight in UTC-8');
SELECT millis_at('in 2 months at midnight in UTC-8');
SELECT micros_at('in 2 months at midnight in UTC-8');
SELECT nanos_at('in 2 months at midnight in UTC-8');

SELECT when_is('last monday at 22:30');
SELECT seconds_at('last monday at 22:30');
SELECT millis_at('last monday at 22:30');
SELECT micros_at('last monday at 22:30');
SELECT nanos_at('last monday at 22:30');

SELECT when_is('December 31, 2026 at evening');
SELECT seconds_at('December 31, 2026 at evening');
SELECT millis_at('December 31, 2026 at evening');
SELECT micros_at('December 31, 2026 at evening');
SELECT nanos_at('December 31, 2026 at evening');

Query Combinations

A pg-when query has up to three components: a date, a time, and a timezone. These components can be combined in several ways, connected by the keywords at and in.

All of the following are valid.

-- all
SELECT when_is('<date> at <time> in <timezone>');
-- date only SELECT when_is('<date>'); SELECT when_is('<date> in <timezone>');
-- time only SELECT when_is('<time>'); SELECT when_is('<time> in <timezone>');
-- date and time SELECT when_is('<date> at <time>');

NOTE: If a timezone is not provided, the system defaults to UTC.

Component Details

<date>

A date can be either relative or exact.

Relative Date

Describes a date in relation to the present.

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

Exact Date

Specifies a calendar date.

• YYYY-MM-DD, YYYY/MM/DD (e.g. 2004-10-10, 2004/10/10)
• DD-MM-YYYY, DD/MM/YYYY (e.g. 10-10-2004, 10/10/2004)
• Month D, YYYY (e.g. January 10, 2004)
• D Month YYYY (e.g. 10 Jan 2004)

<time>

A time can be either relative or exact.

Relative Time

Describes a general or relative time.

• Keywords: noon (12 PM), midnight (12 AM), morning (9 AM), evening (6 PM)
• Phrases: next hour, previous minute, this hour

Exact Date

Specifies aw precise time on the clock.

• AM/PM Format: H:MM:SS AM/PM, H:MM:SS am/pm (e.g. 8:30 pm, 8:30 PM)
• GMT/24-Hour Format: HH:MM:SS GMT, HH:MM:SS (e.g. 15:45 GMT, 15:45)

<timezone>

A timezone can be a named reference or a UTC offset.

Named Timezone

Uses standard IANA timezone names.

• Examples: America/NewYork, Europe/London, Africa/AddisAbaba

UTC Offset

Specifies the hours and minutes offset from Coordinated Universal Time.

• Examples: UTC+3, UTC-08:00, UTC+05:30

Available Functions

The following functions are provided by the Postgres extension.

| Function | Return Type | Description | | ------------------------- | -------------------------- | ---------------------------------------------------- | | when_is | TIMESTAMP WITH TIME ZONE | Returns a standard PostgreSQL timestamp. | | seconds_at | BIGINT | Returns the total seconds since the UNIX epoch. | | millis_at | BIGINT | Returns the total milliseconds since the UNIX epoch. | | micros_at | BIGINT | Returns the total microseconds since the UNIX epoch. | | nanos_at | BIGINT | Returns the total microseconds since the UNIX epoch. |

Usage

Run a PostgreSQL database that has pg-when already installed.

docker run --network=host frectonz/pg-when

pg-when is also distributed with other PostgreSQL versions.

PostgreSQL 13

docker run --network=host frectonz/pg-when:pg13

PostgreSQL 14

docker run --network=host frectonz/pg-when:pg14

PostgreSQL 15

docker run --network=host frectonz/pg-when:pg15

PostgreSQL 16

docker run --network=host frectonz/pg-when:pg16

PostgreSQL 17

docker run --network=host frectonz/pg-when:pg17

PostgreSQL 18

docker run --network=host frectonz/pg-when:pg18