PostgreSQL provides several data types for the DateTime values, such as TIME, DATE, INTERVAL, TIMESTAMP, and TIMESTAMPTZ. These data types allow us to store the DateTime values in a database. The time data type stores time values in the database, the date data type stores the date values, and the interval type stores the intervals. While the TIMESTAMP and TIMESTAMPTZ data types are similar, the only difference is that one includes the time zone information while the other doesn’t.

This blog will show you the working of the Postgres TIMESTAMP data type with suitable examples.

PostgreSQL TIMESTAMP: Timestamp Without Timezone Data Type

The PostgreSQL “TIMESTAMP” or “TIMESTAMP WITHOUT TIME ZONE” data type stores a timestamp value without a timezone. It is mostly used in scenarios where all the users work in the same zones. Use the following syntax to define a column with TIMESTAMP data type:

CREATE TABLE tab_name (
col_name TIMESTAMP
);

To create a table’s column that accepts DateTime values, you need to replace the “col_name” with the column name of your choice and then specify the “TIMESTAMP” data type.

Example 1: Creating a Column With TIMESTAMP Data Type

In the below program, we will create a table named “emp_data” with three columns: “emp_id”, “emp_name”, and “emp_joining_date”:

CREATE TABLE emp_data(
emp_id SMALLINT,
emp_name TEXT,
emp_joining_date TIMESTAMP
);

The above snippet shows that the “emp_joining_date” column is created with the TIMESTAMP data type. So it will store the date time values without timezone information:

Let’s execute the “\d” command to describe the table’s structure:

\d emp_data;

A column named “emp_joining_date” has been successfully created with the “TIMESTAMP WITHOUT TIME ZONE” data type. Let’s learn how to insert DateTime values to a “TIMESTAMP” column in Postgres:

INSERT INTO emp_data(emp_id, emp_name, emp_joining_date)
VALUES(1, 'John', '2021-12-12 09:10:15'),
(2, 'Kane', '2022-01-01 09:00:35'),
(3, 'Williams', '2022-01-15 09:30:00');

Let’s verify the newly inserted records via the “SELECT *” command:

SELECT * FROM emp_data;

The output shows that the DateTime values have been successfully inserted into the TIMESTAMP column.

Example 2: Inserting a DateTime Value Via the LOCALTIMESTAMP Function

The LOCALTIMESTAMP is a built-in function in Postgres that retrieves a DateTime value(without time zone) at which the current transaction begins:

INSERT INTO emp_data(emp_id, emp_name, emp_joining_date)
VALUES(4, 'Mike', LOCALTIMESTAMP);

To verify the newly inserted record, we will execute the SELECT query as follows:

SELECT * FROM emp_data;

A new record without a time zone information has been inserted into the “emp_joining_date” column.

Example 3: Inserting DateTime Values Via Different Built-in Functions

In Postgres, the functions like NOW(), CURRENT_TIMESTAMP, TRANSACTION_TIMESTAMP(), etc., retrieve the current date and time with time zone information. However, if we use them for the TIMESTAMP column, then the timezone information will be skipped, and the date time values will be inserted into the respective column:

INSERT INTO emp_data(emp_id, emp_name, emp_joining_date)
VALUES (5, 'Stephen', NOW()),
(6, 'Ambrose', CURRENT_TIMESTAMP),
(7, 'Shane', TRANSACTION_TIMESTAMP());

Let’s execute the “SELECT *” command one more time to fetch the newly inserted records:

SELECT * FROM emp_data;

The output shows that the current DateTime values have been inserted into the “emp_joining_date” column, and the timezone information has been skipped successfully.

Conclusion

The PostgreSQL “TIMESTAMP” or “TIMESTAMP WITHOUT TIME ZONE” data type stores a timestamp value without the time zone information. In Postgres, the TIMESTAMP and TIMESTAMPTZ data types are similar; the only difference is that one includes the time zone information while the other doesn’t. Inserting a DateTime value with time zone information into the TIMESTAMP column will insert the date and time into the respective column, and the time zone information will be skipped. This blog post explained the working of Postgres’ TIMESTAMP data type with suitable examples.