PostgreSQL – CURRENT_TIMESTAMP Function

The PostgreSQL CURRENT_TIMESTAMP() function returns the current date and time with time zone. It is important to note that the time and time zone returned by this function is from the time the transactions start.

Syntax: CURRENT_TIMESTAMP(precision)

Let’s analyze the above syntax:

• The precision is used to set the number of digits in the fractional seconds precision in the second field of the returned query results. If passes without the precision, it returns the current time (TIMESTAMP type value) that includes the full fractional seconds precision available.
• The CURRENT_TIMESTAMP() function returns a TIMESTAMP WITH TIME ZONE that represents the date and time at which the transaction started.

Example 1:

The following statement depicts the use of the CURRENT_TIMESTAMP() function to query for the current date and time:

SELECT CURRENT_TIMESTAMP;

Output:

Example 2:

First create a table named note that has the created_at column is a TIMESTAMP WITH TIME ZONE column.

CREATE TABLE note(
    note_id serial PRIMARY KEY,
    message varchar(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Thecreated_at column gets its default value from the result of the CURRENT_TIMESTAMP() function. Now, insert some data in the table:

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

Third, verify whether the insert has been taken place correctly using the following query:

SELECT
    *
FROM
    note;

Output: