PostgreSQL offers numerous DateTime functions that retrieve today’s current date and time. Among them, a commonly used function is the “CLOCK_TIMESTAMP()” to get the current DateTime that may change during query execution. Users often use the CLOCK_TIMESTAMP() function with the pg_sleep() function to note the delay time between two statements.

This article will explain the use of the CLOCK_TIMESTAMP() function using different examples.

PostgreSQL CLOCK_TIMESTAMP() Function

The CLOCK_TIMESTAMP() doesn’t accept any argument. To use this function, all you need to do is, invoke it with the SELECT statement, and the rest will be tackled by the function itself. It can also be used with the INSERT query to add the system’s DateTime to a specific table:

CLOCK_TIMESTAMP();

The return type of the stated function is TIMESTAMPTZ, which means it also provides zone information.

Example 1: Using CLOCK_TIMESTAMP() in Postgres

In the following snippet, the CLOCK_TIMESTAMP() function is utilized with the SELECT statement to fetch the current DateTime of the database:

SELECT CLOCK_TIMESTAMP();

The output demonstrates that it's the “1st of May 2023” and the current time is “02:58:27”. While “07” indicates the time zone.

Example 2: Using CLOCK_TIMESTAMP() With pg_sleep in Postgres

The following snippet illustrates the usage of CLOCK_TIMESTAMP() with the pg_sleep() function:

SELECT CLOCK_TIMESTAMP(),
pg_sleep(2),
CLOCK_TIMESTAMP(),
pg_sleep(2),
CLOCK_TIMESTAMP();

That's all about the CLOCK_TIMESTAMP() function in PostgreSQL.

Conclusion

In PostgreSQL, the “CLOCK_TIMESTAMP()” function helps us in getting the current DateTime of a database that may change during query execution. To use this function, all you need to do is, invoke it with the SELECT statement, and the rest will be tackled by the function itself. It can also be used with the INSERT query to add the system’s DateTime to a specific table. The return type of the stated function is TIMESTAMPTZ, which means it also provides zone information. This post has covered various use cases of the Postgres’ CLOCK_TIMESTAMP() function.