PostgreSQL LOCALTIMESTAMP Function

Summary: In this tutorial, you’ll learn how to get the local timestamp using the PostgreSQL LOCALTIMESTAMP function.

PostgreSQL LOCALTIMESTAMP Function Overview #

The LOCALTIMESTAMP function allows you to obtain the current local time as a timestamp without a time zone.

The syntax of the LOCALTIMESTAMP function is straightforward:

LOCALTIMESTAMPCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The LOCALTIMESTAMP can also accept an optional precision parameter:

LOCALTIMESTAMP(precision)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you pass a precision argument to the LOCALTIMESTAMP function, it will round the result to a number of fractional digits in the seconds field.

To retrieve the current time with a time zone, you can use the CURRENT_TIMESTAMP function.

Basic PostgreSQL LOCALTIMESTAMP Function Examples #

The following statement uses the LOCALTIMESTAMP to return the current local time:

SELECT LOCALTIMESTAMP;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

       localtimestamp
----------------------------
 2025-03-20 08:19:37.110453Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To get zero fractional digits in the second field, you can use the precision parameter as follows:

SELECT LOCALTIMESTAMP(0);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

   localtimestamp
---------------------
 2025-03-20 08:19:44Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Understanding How LOCALTIMESTAMP Function Works #

PostgreSQL evaluates the LOCALTIMESTAMP only once per statement execution. For example:

SELECT LOCALTIMESTAMP, pg_sleep(2), LOCALTIMESTAMP;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

       localtimestamp       | pg_sleep |       localtimestamp
----------------------------+----------+----------------------------
 2025-03-20 08:20:00.741931 |          | 2025-03-20 08:20:00.741931Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we called the LOCALTIMESTAMP twice. The output indicates that the pg_sleep(2) caused a delay. However, both LOCALTIMESTAMP function calls in the query returned the same timestamp.

Summary #

Use the LOCALTIMESTAMP function to retrieve the current local time without a time zone.
Use the precision to specify the number of fractional digits in the seconds field.

Quiz #

Was this tutorial helpful ?