info

The CLOCK_TIMESTAMP() function works the same across any PostgreSQL deployment, so you can apply what you learn here wherever you run Postgres. For enterprises building in the AI era, Lakebase delivers a managed Postgres that's fast, secure, and fully integrated into the Lakehouse. For developers and startups who need to ship and scale quickly, Neon is the Postgres platform built to move at your speed.

Summary: in this tutorial, you will learn how to use the PostgreSQL CLOCK_TIMESTAMP() function to return the current date and time.

Introduction to the PostgreSQL CLOCK_TIMESTAMP() function

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

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

CLOCK_TIMESTAMP()

The CLOCK_TIMESTAMP() function has no parameters.

The CLOCK_TIMESTAMP() function returns the current date and time as a timestamp with a timezone.

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

PostgreSQL CLOCK_TIMESTAMP() function examples

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

1) Basic CLOCK_TIMESTAMP() function example

The following example uses the CLOCK_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 the CLOCK_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-07
pg_sleep        |
clock_timestamp | 2024-03-20 14:51:24.924244-07
pg_sleep        |
clock_timestamp | 2024-03-20 14:51:27.931263-07

The output shows that the CLOCK_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 called time_it to measure the execution time of a statement:

CREATE OR REPLACE FUNCTION time_it(
    p_statement TEXT
) RETURNS NUMERIC AS $$
DECLARE
    start_time TIMESTAMP WITH TIME ZONE;
    end_time TIMESTAMP WITH TIME ZONE;
    execution_time NUMERIC; -- ms
BEGIN
    -- 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(EPOCH FROM end_time - start_time) * 1000;

    RETURN execution_time;
END;
$$ LANGUAGE plpgsql;

Second, use the time_it() function to measure the execution time of the statement that uses the pg_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 the CLOCK_TIMESTAMP() function to return the current date and time.