PostgreSQL CLOCK_TIMESTAMP Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL CLOCK_TIMESTAMP function to return the actual current time.

PostgreSQL CLOCK_TIMESTAMP Function Overview #

The CLOCK_TIMESTAMP function returns the actual current time.

Here’s the syntax of the CLOCK_TIMESTAMP function:

CLOCK_TIMESTAMP()Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The CLOCK_TIMESTAMP function requires no parameters, but you must include the parentheses ().

The CLOCK_TIMESTAMP function returns a timestamp with time zone value reflecting the current time.

The actual current time means that if you call the CLOCK_TIMESTAMP function multiple times within a single statement, the return values change.

This is the main difference between the CLOCK_TIMESTAMP function and the STATEMENT_TIMESTAMP function that returns the start time of the current statement.

In practice, you can use the CLOCK_TIMESTAMP to measure query execution time.

PostgreSQL CLOCK_TIMESTAMP() Function Example #

The following example uses the CLOCK_TIMESTAMP() to get the current actual time:

SELECT CLOCK_TIMESTAMP();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

    clock_timestamp
-------------------------------
2025-03-19 20:11:27.720495-07Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output indicates that the resulting timestamp includes a time zone offset.

Calling PostgreSQL CLOCK_TIMESTAMP Multiple Times Within a Statement #

The following statement calls the CLOCK_TIMESTAMP function multiple times within a single statement:

SELECT CLOCK_TIMESTAMP(), pg_sleep(2), CLOCK_TIMESTAMP();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

        clock_timestamp        | pg_sleep |        clock_timestamp
-------------------------------+----------+-------------------------------
 2025-03-19 20:31:56.706461-07 |          | 2025-03-19 20:31:58.711779-07Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output indicates that the actual time changes even within the same statement.

Measuring a Query Execution Time #

The following defines a function that measures the execution time of a query in milliseconds:

CREATE OR REPLACE FUNCTION measure_time (query TEXT)
RETURNS DOUBLE PRECISION
AS
$$
DECLARE
    start_at TIMESTAMP WITH TIME ZONE;
    end_at TIMESTAMP WITH TIME ZONE;
    execution_time_ms DOUBLE PRECISION;
BEGIN
    start_at := clock_timestamp();
    EXECUTE query; -- execute the query
    end_at := clock_timestamp();
    execution_time_ms := EXTRACT(EPOCH FROM (end_at - start_at)) * 1000;

    RETURN execution_time_ms;
END;
$$ LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can call the measure_time function to measure a query like this:

SELECT measure_time('SELECT pg_sleep(2);');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 measure_time
--------------
     2013.297Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the PostgreSQL CLOCK_TIMESTAMP function to return the actual current time.

Quiz #