Contents

• pg-when
  • Query Combinations
  • Component Details
    • <date>
    • <time>
    • <timezone>
  • Available Functions

pg-when

A PostgreSQL extension for creating time values with natural language.

Gets the timestamp for the beginning of the current hour, but 5 days ago, in the Asia/Tokyo timezone.

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');

Finds the time for next Friday at 8:00 PM in the America/New_York timezone.

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');

Calculates the timestamp for exactly 2 months from now, at midnight, in the UTC-8 timezone.

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');

Gets the timestamp for last Monday at a specific 24-hour time: 22:30 (10:30 PM).

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');

Gets the timestamp for the evening (6 PM) on a specific date, New Year’s Eve 2026. This shows how an exact date can be combined with a relative time keyword.

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');

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/New_York, Europe/London, Africa/Addis_Ababa

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. |