{"id":2453,"date":"2025-03-15T19:25:54","date_gmt":"2025-03-15T12:25:54","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2453"},"modified":"2025-03-21T20:23:02","modified_gmt":"2025-03-21T13:23:02","slug":"postgresql-current_timestamp","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-date-functions\/postgresql-current_timestamp\/","title":{"rendered":"PostgreSQL CURRENT_TIMESTAMP"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you’ll learn how to get the current timestamp with a time zone using the PostgreSQL CURRENT_TIMESTAMP<\/code> function.<\/p>\n\n\n\n

PostgreSQL CURRENT_TIMESTAMP Function Overview #<\/a><\/h2>\n\n\n\n

The CURRENT_TIMESTAMP<\/code> function lets you retrieve the current date and time based on the PostgreSQL database server’s time zone settings.<\/p>\n\n\n\n

Typically, you use the CURRENT_TIMESTAMP<\/code> function to set default timestamp values and track time changes.<\/p>\n\n\n\n

Here’s the syntax of the CURRENT_TIMESTAMP<\/code> function:<\/p>\n\n\n

CURRENT_TIMESTAMP<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Alternatively, you can explicitly specify precision:<\/p>\n\n\n
CURRENT_TIMESTAMP<\/span>(precision<\/span>)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the precision<\/code> parameter specifies the number of fractional seconds in the return timestamp.<\/p>\n\n\n\n
The valid value of the precision<\/code> is between 0 (no fractional seconds) and 6 (microsecond precision).<\/p>\n\n\n\n
Retrieving the Current Timestamp #<\/a><\/h2>\n\n\n\n
First, get the server’s time zone:<\/p>\n\n\n
SHOW<\/span> TIMEZONE;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
    TimeZone\n------------------<\/span>\n America\/New_York<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, use the CURRENT_TIMESTAMP<\/code> to retrieve the current date and time:<\/p>\n\n\n
SELECT<\/span> CURRENT_TIMESTAMP<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
      current_timestamp<\/span>\n-------------------------------<\/span>\n 2025<\/span>-03<\/span>-15<\/span> 04<\/span>:23<\/span>:52.330853<\/span>-04<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Your output will differ based on your server time zone and when you execute the statement.<\/p>\n\n\n\n
Using PostgreSQL CURRENT_TIMESTAMP Function with Precision #<\/a><\/h2>\n\n\n\n
The following statement uses the CURRENT_TIMESTAMP<\/code> function with precision:<\/p>\n\n\n
SELECT<\/span> CURRENT_TIMESTAMP<\/span>(2<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
    current_timestamp<\/span>\n---------------------------<\/span>\n 2025<\/span>-03<\/span>-15<\/span> 04<\/span>:25<\/span>:28.65<\/span>-04<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that the returned timestamp only has two fractional seconds because we passed number 2 to the function.<\/p>\n\n\n\n
Setting a Default Value for a Timestamp Column #<\/a><\/h2>\n\n\n\n
In practice, you use the CURRENT_TIMESTAMP<\/code> function to set a default value for a table column. For example:<\/p>\n\n\n\n
First, create a new table<\/a> called sales_orders<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales_orders (\n    id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    customer_id INT<\/span> NOT<\/span> NULL<\/span>,\n    requested_delivery_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    note TEXT<\/span>,\n    created_at TIMESTAMPTZ<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, insert a new row into the sales_orders<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> sales_orders (customer_id, requested_delivery_date, note)\nVALUES<\/span> (1<\/span>, '2025-03-20'<\/span>, 'Please delivery anytime in the morning'<\/span>)\nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Displaying Timestamp in a Different Time Zone #<\/a><\/h2>\n\n\n\n
The value of the CURRENT_TIMESTAMP<\/code> function automatically changes based on the session’s time zone. For example:<\/p>\n\n\n\n
First, change the timezone to 'America\/Los_Angeles'<\/code>:<\/p>\n\n\n
SET<\/span> TIME<\/span> ZONE<\/span> 'America\/Los_Angeles'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, retrieve the current timestamp of the new timezone:<\/p>\n\n\n
SELECT<\/span> CURRENT_TIMESTAMP<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
      current_timestamp<\/span>\n-------------------------------<\/span>\n 2025<\/span>-03<\/span>-15<\/span> 01<\/span>:37<\/span>:38.055455<\/span>-07<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
To convert timestamps between time zones, use AT TIME ZONE<\/code>:<\/p>\n\n\n
SELECT<\/span> CURRENT_TIMESTAMP<\/span> AT TIME ZONE<\/span> 'UTC'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
        timezone\n----------------------------<\/span>\n 2025<\/span>-03<\/span>-15<\/span> 08<\/span>:38<\/span>:09.730509<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
When you insert a new row<\/a> into the sales_orders<\/code> without specifying created_at<\/code>, PostgreSQL automatically fills it with the current timestamp.<\/p>\n\n\n\n
The CURRENT_TIMESTAMP<\/code> function returns a TIMESTAMP WITH TIME ZONE<\/a><\/code> value representing the current date and time when you call the function.<\/p>\n\n\n\n