PostgreSQL CURRENT_TIMESTAMP

Summary: In this tutorial, you’ll learn how to get the current timestamp with a time zone using the PostgreSQL CURRENT_TIMESTAMP function.

PostgreSQL CURRENT_TIMESTAMP Function Overview #

The CURRENT_TIMESTAMP function lets you retrieve the current date and time based on the PostgreSQL database server’s time zone settings.

Typically, you use the CURRENT_TIMESTAMP function to set default timestamp values and track time changes.

Here’s the syntax of the CURRENT_TIMESTAMP function:

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

Alternatively, you can explicitly specify precision:

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

In this syntax, the precision parameter specifies the number of fractional seconds in the return timestamp.

The valid value of the precision is between 0 (no fractional seconds) and 6 (microsecond precision).

The CURRENT_TIMESTAMP function returns a TIMESTAMP WITH TIME ZONE value representing the current date and time when you call the function.

Retrieving the Current Timestamp #

First, get the server’s time zone:

SHOW TIMEZONE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

    TimeZone
------------------
 America/New_YorkCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, use the CURRENT_TIMESTAMP to retrieve the current date and time:

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

Output:

      current_timestamp
-------------------------------
 2025-03-15 04:23:52.330853-04Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Your output will differ based on your server time zone and when you execute the statement.

Using PostgreSQL CURRENT_TIMESTAMP Function with Precision #

The following statement uses the CURRENT_TIMESTAMP function with precision:

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

Output:

    current_timestamp
---------------------------
 2025-03-15 04:25:28.65-04Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Notice that the returned timestamp only has two fractional seconds because we passed number 2 to the function.

Setting a Default Value for a Timestamp Column #

In practice, you use the CURRENT_TIMESTAMP function to set a default value for a table column. For example:

First, create a new table called sales_orders:

CREATE TABLE sales_orders (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL,
    requested_delivery_date DATE NOT NULL,
    note TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

When you insert a new row into the sales_orders without specifying created_at, PostgreSQL automatically fills it with the current timestamp.

Second, insert a new row into the sales_orders table:

INSERT INTO sales_orders (customer_id, requested_delivery_date, note)
VALUES (1, '2025-03-20', 'Please delivery anytime in the morning')
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Displaying Timestamp in a Different Time Zone #

The value of the CURRENT_TIMESTAMP function automatically changes based on the session’s time zone. For example:

First, change the timezone to 'America/Los_Angeles':

SET TIME ZONE 'America/Los_Angeles';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, retrieve the current timestamp of the new timezone:

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

Output:

      current_timestamp
-------------------------------
 2025-03-15 01:37:38.055455-07Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To convert timestamps between time zones, use AT TIME ZONE:

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

        timezone
----------------------------
 2025-03-15 08:38:09.730509Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• The CURRENT_TIMESTAMP function returns the current date and time of the PostgreSQL server as a TIMESTAMP WITH TIME ZONE.

Quiz #