In PostgreSQL, the localtimestamp() function returns the local date and time, without the time zone offset.

If you need the time zone offset, use current_timestamp() instead.

Syntax

You can use it in either of the following ways:

localtimestamp
localtimestamp(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 localtimestamp;

Result:

2020-07-01 15:55:48.039777

Example – Specific Precision

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

SELECT localtimestamp(0);

Result:

2020-07-01 15:56:02

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 localtimestamp(3);

Result:

2020-07-01 15:56:14.771

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 localtimestamp;
SELECT pg_sleep(5);
SELECT localtimestamp;
SELECT pg_sleep(5);
SELECT localtimestamp;
COMMIT;

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

postgres=# BEGIN;
BEGIN
postgres=# SELECT localtimestamp;
      localtimestamp      
--------------------------
 2020-07-02 09:25:38.3858
(1 row)


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


postgres=# SELECT localtimestamp;
      localtimestamp      
--------------------------
 2020-07-02 09:25:38.3858
(1 row)


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


postgres=# SELECT localtimestamp;
      localtimestamp      
--------------------------
 2020-07-02 09:25:38.3858
(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 
  localtimestamp,
  pg_sleep(5),
  localtimestamp,
  pg_sleep(5),
  localtimestamp;

Result (using vertical output):

localtimestamp | 2020-07-02 09:26:46.743801
pg_sleep       | 
localtimestamp | 2020-07-02 09:26:46.743801
pg_sleep       | 
localtimestamp | 2020-07-02 09:26:46.743801

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

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

In this example, 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.