Postgres supports various temporal data types, such as DATE, TIMESTAMP, TIME, etc. Among them, the TIMESTAMP and TIMESTAMPTZ are used to store the date and time values with or without time zone information. A table’s column created with TIMESTAMP or TIMESTAMPTZ data type has the ability to store the current timestamp or any specific timestamp.

This Postgres blog presents a detailed guide on how to insert the current or any specific timestamp in Postgres.

How to Insert a Timestamp into a Postgres Table?

The need for the TIMESTAMP data type arises when we have to keep/save the date and time values in a particular database. For instance, the TIMESTAMP data type can be handy in scenarios where we have to maintain the staff’s check-in and check-out record, the customer’s order placement DateTime, the flight’s arrival and departure record, etc.

Syntax

Utilize the following syntax to create/define a table’s column with TIMESTAMP data type:

CREATE TABLE tab_name(
col_name TIMESTAMP | TIMESTAMPTZ constraint
);

- col_name represents a column to be defined with the TIMESTAMP data type.
- Use either TIMESTAMP or TIMESTMAPTZ data type.
- Specify the constraint of your choice such as PRIMARY KEY, CHECK, UNIQUE, etc. in place of constraint.

Example 1: Creating a Table Column With Timestamp

Let’s create a sample table named staff_info with the following columns: e_id, e_name, and e_joining_date:

CREATE TABLE staff_info(
e_id INT PRIMARY KEY,
e_name TEXT,
e_joining_date TIMESTAMP
);

The “staff_info” table with the specified columns has been successfully created.

Note: Use the TIMESTAMPTZ data type instead of the TIMESTAMP to store the DateTime values along with the time zone.

Example 2: Inserting a Specific Timestamp Into a Postgres Table

Use the INSERT query to insert a particular timestamp into the “staff_info” table:

INSERT INTO staff_info(e_id, e_name, e_joining_date)
VALUES (1, 'Joe', '2022-10-10 11:30:30');

Let’s fetch the newly inserted record via the “SELECT” command:

SELECT * FROM staff_info;

A particular timestamp has been inserted into the staff_info table.

Example 3: Inserting the Current Timestamp Into a Postgres Table

The inbuilt date functions like NOW(), CURRENT_TIMESTAMP and LOCALTIMESTAMP can be used with the INSERT statement to insert the current timestamp into a Postgres table:

INSERT INTO staff_info(e_id, e_name, e_joining_date)
VALUES (2, 'Natie', CURRENT_TIMESTAMP),
(3, 'Seth', LOCALTIMESTAMP),
(4, 'Joseph', NOW());

In the above query, three different DateTime functions are used to insert the current timestamp in the “staff_info” table:

To confirm the newly inserted records, utilize the “SELECT *” command:

SELECT * FROM staff_info;

This way, you can insert the current timestamp into any specific Postgres table.

Conclusion

In PostgreSQL, the TIMESTAMP and TIMESTAMPTZ are used to store the date and time values with or without time zone information. The inbuilt date functions like NOW(), CURRENT_TIMESTAMP, and LOCALTIMESTAMP, are used with the INSERT statement to add the current timestamp into a Postgres table. This post considered different examples to explain how we can insert a specific or current timestamp into a Postgres table.