In PostgreSQL, the current_time function returns the current time, including the time zone offset.

The time returned is based on the start time of the current transaction.

Syntax

You can use it in either of the following ways:

current_time
current_time(x)

Where x is an optional precision parameter, which causes the result to be rounded to x number of fractional digits in the seconds field.

Without a precision parameter (the first syntax), the result is given to the full available precision.

Example – Full Precision

Here’s a basic example to demonstrate a result using the full available precision.

SELECT current_time;

Result:

14:33:30.731384+10

Example – Specific Precision

Here’s an example to demonstrate how to specify the precision.

SELECT current_time(0);

Result:

15:35:38+10

In this example, I specified a precision of zero, which means that no fractional seconds were returned.

Here’s another example where I specify a number of fractional seconds.

SELECT current_time(3);

Result:

15:37:43.385+10

Transactions

The actual time is based on the start time of the current transaction. Therefore, it doesn’t change as the transaction progresses.

This allows a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.

Here’s an example that demonstrates this.

BEGIN;
SELECT current_time;
SELECT pg_sleep(5);
SELECT current_time;
SELECT pg_sleep(5);
SELECT current_time;
COMMIT;

Here’s the full output of my terminal when executing that transaction in psql:

postgres=# BEGIN;
BEGIN
postgres=# SELECT current_time;
    current_time    
--------------------
 09:09:27.524852+10
(1 row)


postgres=# SELECT pg_sleep(5);
SELECT current_time;
SELECT pg_sleep(5);
SELECT current_time;
COMMIT;
 pg_sleep 
----------
 
(1 row)


postgres=# SELECT current_time;
    current_time    
--------------------
 09:09:27.524852+10
(1 row)


postgres=# SELECT pg_sleep(5);
 pg_sleep 
----------
 
(1 row)


postgres=# SELECT current_time;
    current_time    
--------------------
 09:09:27.524852+10
(1 row)


postgres=# COMMIT;
COMMIT

All three time values are identical, even though the pg_sleep() function was used to delay execution between each statement within the transaction.

This is in contrast to statement_timestamp() which does change with each statement.

Multiple Calls within a Statement

It also doesn’t change as the statement progresses.

\x
SELECT 
  current_time,
  pg_sleep(5),
  current_time,
  pg_sleep(5),
  current_time;

Result (using vertical output):

current_time | 09:11:16.09445+10
pg_sleep     | 
current_time | 09:11:16.09445+10
pg_sleep     | 
current_time | 09:11:16.09445+10

All three time values are identical, even though the pg_sleep() function was used to delay execution between each call to current_time.

This is in contrast to the clock_timestamp() function, which does change as it progresses through the statement.

By the way, I used vertical output (also known as expanded output) to make the results slightly more compact.

You can toggle expanded output in psql using \x.