In PostgreSQL, clock_timestamp() is a non-SQL-standard time function that returns the current date and time.

An important consideration of this function is that its result changes throughout the execution of a statement. Therefore, you could get a different result in different parts of the statement if you call the function multiple times within a single statement.

Syntax

The syntax is as simple as it gets:

clock_timestamp()

So it doesn’t accept any parameters.

Example

Here’s a basic example to demonstrate.

SELECT clock_timestamp();

Result:

2020-07-01 09:53:22.823731+10

Multiple Calls

Here’s a basic example to demonstrate how the results can differ when you call the function multiple times within a single SQL statement.

\x
SELECT 
  clock_timestamp(),
  pg_sleep(5),
  clock_timestamp(),
  pg_sleep(3),
  clock_timestamp();

Result (using vertical output):

clock_timestamp | 2020-07-01 09:58:29.744838+10
pg_sleep        |
clock_timestamp | 2020-07-01 09:58:34.813448+10
pg_sleep        |
clock_timestamp | 2020-07-01 09:58:37.859197+10

Here, I used the pg_sleep() function to delay execution for several seconds. The first call delays execution for 5 seconds and the second call delays execution for 3 seconds.

We can see that each time clock_timestamp() was called, the actual time was slightly different. This is mainly due to the pg_sleep() function, but it could still be different without it, depending on how fast the query runs.

Here it is again without the pg_sleep() calls.

SELECT 
  clock_timestamp(),
  clock_timestamp(),
  clock_timestamp();

Result (using vertical output):

clock_timestamp | 2020-07-01 10:03:10.828557+10
clock_timestamp | 2020-07-01 10:03:10.828558+10
clock_timestamp | 2020-07-01 10:03:10.828558+10

These examples use vertical output (usually called expanded display in psql) to make it easier to read the results.

You can toggle expanded display in psql with \x.