Introduction to PostgreSQL CURRENT_TIMESTAMP() function

TheCURRENT_TIMESTAMP function returns the current date and time with the timezone.

Here’s the basic syntax of the PostgreSQL CURRENT_TIMESTAMP() function:

CURRENT_TIMESTAMP(precision)

The PostgreSQL CURRENT_TIMESTAMP() function accepts one optional argument:

• precision: specifies the number of digits in the fractional seconds precision in the second field of the result.

If you omit theprecision argument, theCURRENT_TIMESTAMP() function will return aTIMESTAMP with a timezone that includes the full fractional seconds precision available.

TheCURRENT_TIMESTAMP() function returns aTIMESTAMP WITH TIME ZONE representing the date and time at which the transaction started.

PostgreSQL CURRENT_TIMESTAMP function examples

Let’s explore some examples of using theCURRENT_TIMESTAMP function.

1) Basic CURRENT_TIMESTAMP function example

The following example shows how to use theCURRENT_TIMESTAMP() function to get the current date and time:

SELECT CURRENT_TIMESTAMP;

The result is:

current_timestamp------------------------------- 2025-12-12 19:02:31.18598+00(1 row)

TheCURRENT_TIMESTAMP() function is equivalent to theNOW() function as both return the timestamp at which the current transaction started.

Like theNOW() function,CURRENT_TIMESTAMP() can be used as the default value of a timestamp column.

2) Using the PostgreSQL CURRENT_TIMESTAMP function as the default value of a column

First,create a table callednote:

CREATE TABLE note (    idSERIAL PRIMARY KEY,    message VARCHAR(255)NOT NULL,    created_atTIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);

The default value of thecreated_at column is provided by the result of theCURRENT_TIMESTAMP() function.

Second,insert a new row into thenote table:

INSERT INTO note(message)VALUES('Testing current_timestamp function');

In this statement, we don’t specify the value of thecreated_at column. Therefore, it takes the result of theCURRENT_TIMESTAMP at which the transaction started.

Third, verify the insert:

SELECT * FROM note;

The following picture illustrates the result:

id |              message               |          created_at----+------------------------------------+-------------------------------  1 | Testing current_timestamp function | 2024-01-26 15:47:44.199212-07(1 row)

The output indicates that thecreated_at column is populated by the date and time at which the statement was executed.

In PostgreSQL, the TRANSACTION_TIMESTAMP() function is synonymous with theCURRENT_TIMESTAMP function. However, the name of the functionTRANSACTION_TIMESTAMP more explicitly conveys the meaning of the return value.

Summary

• Use the PostgreSQLCURRENT_TIMESTAMP() to get the date and time at which the transaction starts.

Last updated onJanuary 26, 2024

On this page

• Introduction to PostgreSQL CURRENT_TIMESTAMP() function
• PostgreSQL CURRENT_TIMESTAMP function examples
• Summary