In PostgreSQL, you can use the pg_sleep_until() function to delay execution until a specified timestamp.

This is useful for when a specific wake-up time is desired.

Note that pg_sleep_until is not guaranteed to wake up exactly at the specified time, but it will not wake up any earlier.

Syntax

The syntax goes like this:

pg_sleep_until(timestamp with time zone)

Example

Here’s an example to demonstrate its usage.

\x
SELECT 
  clock_timestamp(),
  pg_sleep_until('today 15:30'),
  clock_timestamp();

Result (using vertical output):

clock_timestamp | 2020-06-28 15:29:54.564608+10
pg_sleep_until  |
clock_timestamp | 2020-06-28 15:30:00.0119+10

I used clock_timestamp() in this example, because it changes during statement execution. This allows us to see the updated values as the statement progresses.

I used expanded display (sometimes referred to as “vertical output”) in this example in order to make it easier to see the result.

You can toggle expanded display in psql by using \x.

Example 2

Here’s another example, this time with an extra call to pg_sleep_until() and clock_timestamp().

SELECT 
  clock_timestamp(),
  pg_sleep_until('today 15:32'),
  clock_timestamp(),
  pg_sleep_until('today 15:33'),
  clock_timestamp();

Result (using vertical output):

clock_timestamp | 2020-06-28 15:31:23.142358+10
pg_sleep_until  |
clock_timestamp | 2020-06-28 15:32:00.061566+10
pg_sleep_until  |
clock_timestamp | 2020-06-28 15:33:00.074381+10

Example 3

In this example, I explicitly specify the date/time and timezone offset.

Also, I run the query after the first timestamp has passed. Therefore it runs immediately.

SELECT 
  clock_timestamp(),
  pg_sleep_until('2020-06-29 08:54:00.000000+10'),
  clock_timestamp(),
  pg_sleep_until('2020-06-29 08:55:00.000000+10'),
  clock_timestamp();

Result (using vertical output):

clock_timestamp | 2020-06-29 08:54:17.189189+10
pg_sleep_until  |
clock_timestamp | 2020-06-29 08:54:17.189196+10
pg_sleep_until  |
clock_timestamp | 2020-06-29 08:55:00.062783+10

As the result shows, I didn’t start running the query until 17 seconds past the first pg_sleep_until() value, and so it ran immediately and used the timestamp at the time it ran.

My timestamps were all in quick succession in these examples, because I didn’t want to wait hours or days just to update this article. But you can go ahead and specify a different date if required.