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
.