Introduction to the PostgreSQL CLOCK_TIMESTAMP() function

TheCLOCK_TIMESTAMP() function returns the current date and time with a timezone.

Here’s the basic syntax of theCLOCK_TIMESTAMP() function:

CLOCK_TIMESTAMP()

TheCLOCK_TIMESTAMP() function has no parameters.

TheCLOCK_TIMESTAMP() function returns the current date and time as atimestamp with a timezone.

When you call theCLOCK_TIMESTAMP() function multiple times within a statement, you’ll get different results.

PostgreSQL CLOCK_TIMESTAMP() function examples

Let’s take some examples of using theCLOCK_TIMESTAMP() function.

1) Basic CLOCK_TIMESTAMP() function example

The following example uses theCLOCK_TIMESTAMP() function to obtain the current date and time:

SELECT CLOCK_TIMESTAMP();

Output:

clock_timestamp------------------------------- 2024-03-20 14:49:07.875891-07(1 row)

The result is a timestamp with a time zone.

2) Calling CLOCK_TIMESTAMP() function multiple times within a statement

The following example calls theCLOCK_TIMESTAMP() function multiple times within a statement:

SELECT  clock_timestamp(),  pg_sleep(3),  clock_timestamp(),  pg_sleep(3),  clock_timestamp();

Output:

-[ RECORD 1 ]---+------------------------------clock_timestamp |2024-03-20 14:51:21.92144-07pg_sleep        |clock_timestamp |2024-03-20 14:51:24.924244-07pg_sleep        |clock_timestamp |2024-03-20 14:51:27.931263-07

The output shows that theCLOCK_TIMESTAMP() function returns the actual date and time between the calls within the same statement.

3) Using the CLOCK_TIMESTAMP() function to measure the execution time of a statement

First,define a new function calledtime_it to measure the execution time of a statement:

CREATE OR REPLACE FUNCTION time_it(    p_statementTEXT)RETURNS NUMERIC AS $$DECLARE    start_timeTIMESTAMP WITH TIME ZONE;    end_timeTIMESTAMP WITH TIME ZONE;    execution_timeNUMERIC;-- msBEGIN    -- Capture start time    start_time := CLOCK_TIMESTAMP();    -- Execute the statement    EXECUTE p_statement;    -- Capture end time    end_time := CLOCK_TIMESTAMP();    -- Calculate execution time in milliseconds    execution_time := EXTRACT(EPOCHFROM end_time- start_time)* 1000;    RETURN execution_time;END;$$LANGUAGE plpgsql;

Second, use thetime_it() function to measure the execution time of the statement that uses thepg_sleep() function:

SELECT time_it('SELECT pg_sleep(1)');

Output:

time_it------------- 1007.731000

It takes about 1008 ms or 1s to complete.

Summary

• Use theCLOCK_TIMESTAMP() function to return the current date and time.

Last updated onMarch 20, 2024

On this page

• Introduction to the PostgreSQL CLOCK_TIMESTAMP() function
• PostgreSQL CLOCK_TIMESTAMP() function examples
• Summary