{"id":2506,"date":"2025-03-20T10:33:13","date_gmt":"2025-03-20T03:33:13","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2506"},"modified":"2025-03-21T20:52:25","modified_gmt":"2025-03-21T13:52:25","slug":"postgresql-clock_timestamp","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-date-functions\/postgresql-clock_timestamp\/","title":{"rendered":"PostgreSQL CLOCK_TIMESTAMP Function"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you’ll learn how to use the PostgreSQL CLOCK_TIMESTAMP<\/code> function to return the actual current time.<\/p>\n\n\n\n

PostgreSQL CLOCK_TIMESTAMP Function Overview #<\/a><\/h2>\n\n\n\n

The CLOCK_TIMESTAMP<\/code> function returns the actual current time.<\/p>\n\n\n\n

Here’s the syntax of the CLOCK_TIMESTAMP<\/code> function:<\/p>\n\n\n

CLOCK_TIMESTAMP()<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The CLOCK_TIMESTAMP<\/code> function requires no parameters, but you must include the parentheses ()<\/code>.<\/p>\n\n\n\n
The CLOCK_TIMESTAMP<\/code> function returns a timestamp with time zone<\/code> value reflecting the current time.<\/p>\n\n\n\n
The actual current time means that if you call the CLOCK_TIMESTAMP<\/code> function multiple times within a single statement, the return values change.<\/p>\n\n\n\n
In practice, you can use the CLOCK_TIMESTAMP<\/code> to measure query execution time.<\/p>\n\n\n\n
PostgreSQL CLOCK_TIMESTAMP() Function Example #<\/a><\/h2>\n\n\n\n
The following example uses the CLOCK_TIMESTAMP()<\/code> to get the current actual time:<\/p>\n\n\n
SELECT<\/span> CLOCK_TIMESTAMP();<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
\n    clock_timestamp\n-------------------------------<\/span>\n2025<\/span>-03<\/span>-19<\/span> 20<\/span>:11<\/span>:27.720495<\/span>-07<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the resulting timestamp includes a time zone offset.<\/p>\n\n\n\n
Calling PostgreSQL CLOCK_TIMESTAMP Multiple Times Within a Statement #<\/a><\/h2>\n\n\n\n
The following statement calls the CLOCK_TIMESTAMP<\/code> function multiple times within a single statement:<\/p>\n\n\n
SELECT<\/span> CLOCK_TIMESTAMP(), pg_sleep(2<\/span>), CLOCK_TIMESTAMP();<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
        clock_timestamp        | pg_sleep |        clock_timestamp\n-------------------------------+----------+-------------------------------<\/span>\n 2025<\/span>-03<\/span>-19<\/span> 20<\/span>:31<\/span>:56.706461<\/span>-07<\/span> |          | 2025<\/span>-03<\/span>-19<\/span> 20<\/span>:31<\/span>:58.711779<\/span>-07<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the actual time changes even within the same statement.<\/p>\n\n\n\n
Measuring a Query Execution Time #<\/a><\/h2>\n\n\n\n
The following defines a function<\/a> that measures the execution time of a query in milliseconds:<\/p>\n\n\n
CREATE<\/span> OR REPLACE<\/span> FUNCTION<\/span> measure_time (query TEXT<\/span>)\nRETURNS<\/span> DOUBLE<\/span> PRECISION<\/span>\nAS<\/span>\n$$\nDECLARE<\/span>\n    start_at TIMESTAMP<\/span> WITH TIME ZONE<\/span>;\n    end_at TIMESTAMP<\/span> WITH TIME ZONE<\/span>;\n    execution_time_ms DOUBLE<\/span> PRECISION<\/span>;\nBEGIN<\/span>\n    start_at := clock_timestamp();\n    EXECUTE<\/span> query; -- execute the query<\/span>\n    end_at := clock_timestamp();\n    execution_time_ms := EXTRACT(EPOCH<\/span> FROM<\/span> (end_at - start_at)) * 1000<\/span>;\n\n    RETURN<\/span> execution_time_ms;\nEND<\/span>;\n$$<\/span> LANGUAGE<\/span> plpgsql;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can call the measure_time<\/code> function to measure a query like this:<\/p>\n\n\n
SELECT<\/span> measure_time('SELECT pg_sleep(2);'<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 measure_time\n--------------<\/span>\n     2013.297<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
This is the main difference between the CLOCK_TIMESTAMP<\/code> function and the STATEMENT_TIMESTAMP<\/a><\/code> function that returns the start time of the current statement.<\/p>\n\n\n\n