In PostgreSQL, the current_timestamp() function returns the current date and time, including the time zone offset.

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

Syntax

You can use it in either of the following ways:

current_timestamp
current_timestamp(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_timestamp;

Result:

2020-07-01 15:42:35.630594+10

Example – Specific Precision

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

SELECT current_timestamp(0);

Result:

2020-07-01 15:42:58+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_timestamp(3);

Result:

2020-07-01 15:43:16.644+10

Transactions

The actual timestamp 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_timestamp;
SELECT pg_sleep(5);
SELECT current_timestamp;
SELECT pg_sleep(5);
SELECT current_timestamp;
COMMIT;

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

postgres=# BEGIN;
BEGIN
postgres=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2020-07-02 09:02:52.952669+10
(1 row)


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


postgres=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2020-07-02 09:02:52.952669+10
(1 row)


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


postgres=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2020-07-02 09:02:52.952669+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_timestamp,
  pg_sleep(5),
  current_timestamp,
  pg_sleep(5),
  current_timestamp;

Result (using vertical output):

current_timestamp | 2020-07-02 09:04:03.413062+10
pg_sleep          | 
current_timestamp | 2020-07-02 09:04:03.413062+10
pg_sleep          | 
current_timestamp | 2020-07-02 09:04:03.413062+10

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

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.