PostgreSQL Current Timestamp with Examples

PostgreSQL provides various date and time functions. One of the common functions is CURRENT_TIMESTAMP. PostgreSQL provides the CURRENT_TIMSTAMP function to show the current timestamp. The timestamp includes both the date and time information, along with time zone information. We can use the CURRENT_TIMSTAMP function in select queries and insert or update commands.

Current Timestamp in SELECT Queries

To show the current date-time in select queries, use the CURRENT_TIMESTAMP function.

SELECT CURRENT_TIMESTAMP;

Don’t use round brackets () after the CURRENT_TIMSTAMP function.

The current timestamp function returns the current date and time, along with the time zone information. To get it without a time zone, you need to cast it to a timestamp data type. Below is an example.

SELECT CURRENT_TIMESTAMP::TIMESTAMP;

The CURRENT_TIMESTAMP function also returns the microseconds after the seconds. For example, in the above screenshot, microseconds are 07551. To get the current timestamp without microseconds, put the 0 in round brackets after the CURRENT_TIMESTAMP function, as shown below.

SELECT CURRENT_TIMESTAMP(0);

CURRENT_TIMESTAMP in Create Table

We can also use the current timestamp function to pass the default value for the column while creating the table. We need to pass the DEFAULT CURRENT_TIMESTAMP statement after the date type information. Below is an example.

CREATE TABLE employee
(
	id serial primary key,
	name varchar(20),
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
)

Use of CURRENT_TIMESTAMP in INSERT/UPDATE Commands

In the above create table, we are creating a timestamp column whose default value is the current timestamp.

Insert Command

When inserting data into the table, we don’t need to pass a timestamp column, or we can use the DEFAULT keyword instead of passing date time, as shown below.

INSERT INTO employee (name) VALUES ('NAME 1');
INSERT INTO employee (name, timestamp) VALUES ('NAME 2', DEFAULT);

Both insert queries are valid and will work correctly.

Update Command

We can also update the timestamp column in the update command without providing date time information. We need to use the DEFAULT keyword for the timestamp column. Below is an example.

UPDATE employee SET timestamp = DEFAULT WHERE id = 1;

Summary

CURRENT_TIMESTAMP returns the system’s current timestamp information with the time zone. We can use the current timestamp function in the create table as well as insert and update commands.

Read More:

• Delete Column in PostgreSQL with Examples
• PostgreSQL Alter Column Type with Examples