PostgreSQL facilitates us with two temporal data types to deal with the TIMESTAMPS. For instance, TIMESTAMP and TIMESTAMPTZ: the first one stores the TIMESTAMP without the timezone, while the other one stores the TIMESTAMP with a timezone. Both these data types consume 8 bytes to store a TIMESTAMP value.

TIMESTAMP data types and functions will be explained in this Postgres blog using Practical examples. So, let’s get started!

PostgreSQL TIMESTAMP Data Types

In Postgres, the TIMESTAMP data type is used to store the date and time without any time zone information. Therefore, changing a database's timezone will not update the timestamp values already stored in the database. The below snippet demonstrates how to create a column with TIMESTAMP data type:

CREATE TABLE tab_name (
col_name TIMESTAMP
);

Specify the table name in place of “tab_name”. Next, specify the column name followed by the TIMESTAMP data type.

Use the Postgres’ TIMESTAMPTZ data type to store the date, time, and time zone information. The below syntax shows the usage of the TIMESTAMPTZ data type:

CREATE TABLE tab_name (
col_name TIMESTAMPTZ
);

Note: A value inserted into a column declared with the TIMESTAMPTZ data type will be converted into a universal time-coordinated value(UTC). While Postgres converts the UTC value back to the TIMESTAMPTZ type when someone queries that value from the database.

Example 1: How to Create a Table Using TIMESTAMP and TIMESTAMPTZ in Postgres?

To create a table in Postgres with a TIMESTAMP and TIMESTAMPTZ column, you can use the CREATE TABLE statement as follows:

CREATE TABLE timestamp_example(
time_ts TIMESTAMP NOT NULL,
time_tz TIMESTAMPTZ NOT NULL
);

The output snippet proves that the “timestamp_example” table has been created successfully. Let’s learn how to insert data into timestamp columns:

INSERT INTO timestamp_example(time_ts, time_tz)
VALUES ('2022-12-22 01:13:41.104131', '2022-12-22 01:13:41.104131-08');

The above snippet verifies that the INSERT INTO command was executed successfully. Use the “SELECT *” command to verify the newly inserted data:

SELECT * FROM timestamp_example;

The output shows that the TIMESTAMP with timezone and without timezone has been inserted into the respective columns.

Example 2: How to Set the Current TIMESTAMP as a Column Default Value?

Use the DEFAULT keyword along with the CURRENT_TIMESTAMP function to set the current timestamp as the default value of a column:

CREATE TABLE emp_example(
emp_id SERIAL PRIMARY KEY,
emp_name TEXT,
emp_login TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

The CURRENT_TIMESTAMP function retrieves the time with the timezone, so by default, the emp_login column will accept the current date, time, and timezone:

The above snippet shows that the “emp_example” table has been created successfully. Now, execute the “INSERT INTO” command to insert the employee’s data into the “emp_example” table:

INSERT INTO emp_example(emp_name)
VALUES('Joseph'),
('Joe'),
('Alexa'),
('Natie'),
('Alex');

Five records have been inserted into the “emp_example” table. Let’s query the inserted data using the “SELECT *” command:

SELECT * FROM emp_example;

The output snippet proves that the current timestamp has been successfully inserted into the “emp_login” column.

Postgres TIMESTAMP Functions

PostgreSQL provides several functions that allow you to manipulate and work with timestamps. Here are some frequently used timestamp functions that you might find useful:

• CURRENT_TIMESTAMP: Retrieves the current date and time as a timestamp.
• NOW(): Retrieves today’s date and time as a TIMESTAMP.
• DATE_TRUNC(field, timestamp): Truncates/trims a timestamp to the specified field, such as a month, year, etc.
• TO_TIMESTAMP(time_string, format): Converts a string to a timestamp according to the given format.
• AGE(timestamp_1, timestamp_2): Calculates the difference between two timestamps and returns the result as an interval.
• EXTRACT(field FROM timestamp): Extracts a particular field, such as a month, day, year, etc., from a timestamp.

Note: A wide range of TIMESTAMP functions are available in Postgres; only a few are discussed here. You can visit the following Postgres guide to learn more about the TIMESTAMP functions.

Conclusion

PostgreSQL provides two temporal data types to deal with the TIMESTAMPS: TIMESTAMP and TIMESTAMPTZ. The first one stores the TIMESTAMP without the timezone, while the second one stores the TIMESTAMP with a timezone. Moreover, Postgres provides various built-in timestamp functions that allow us to manipulate and work with timestamps, such as NOW(), CURRENT_TIMESTAMP, AGE(), etc. This Postgres guide presented a thorough guide on using the TIMESTAMP data types and Functions in Postgres.