PostgreSQL facilitates us with various temporal data types, such as TIME, DATE, INTERVAL, TIMESTAMP, and TIMESTAMPTZ. These data types enable us to store the dates and times in a database. 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 post will explain the working of the Postgres TIMESTAMPTZ data type with practical examples.

PostgreSQL Timestamp With Timezone(TIMESTAMPTZ) Data Type

The PostgreSQL “TIMESTAMPTZ” or “TIMESTAMP With TIME ZONE” data type is used to store a timestamp value that includes the time zone information. This data type is useful in global applications where the users' time zones may differ. Postgres’ default time zone is UTC; therefore, inserting any value in the “TIMESTAMP With TIME ZONE” data type column will be converted into UTC.

Syntax

To define a column with the “TIMESTAMPTZ” data type, you need to specify the column name followed by the TIMESTAMPTZ data type:

CREATE TABLE tab_name (
col_name TIMESTAMPTZ
 );

Let’s comprehend the TIMESTAMPTZ data type via the following examples.

Example: Creating a Column With TIMESTAMPTZ Data Type

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

CREATE TABLE employee_data(
emp_id SMALLINT,
emp_name TEXT,
emp_joining_date TIMESTAMPTZ
);

Here in the above snippet, the “emp_joining_date” column is created with the “TIMESTAMPTZ” data type:

Execute the “\d” command along with the table’s name to find the details of the newly created table:

\d employee_data;

A column named “emp_joining_date” with “timestamp with time zone” data type has been created successfully. Now, let’s learn how to insert data into the “TIMESTAMPTZ” column:

INSERT INTO   employee_data(emp_id, emp_name, emp_joining_date)
VALUES (1, 'Joe', '2021-01-09   05:13:15.162085-08');

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

SELECT * FROM employee_data;

The output signifies that a timestamp with the time zone information has been inserted into the TIMESTAMPTZ column.

TIMESTAMPTZ Functions

Postgres offers several date-time functions to deal with temporal data. Some functions with the return type “TIMESTAMPTZ” have been listed below:

- NOW(): Retrieves the current DateTime with timezone information.
- CURRENT_TIMESTAMP: Retrieves the timestamp value with timezone information.
- TO_TIMESTAMP(): Converts a DateTime string to a timestamp. Its return type is TIMESTAMPTZ.
- CLOCK_TIMESTAMP(): Retrieves the current DateTime with timezone information at which the recent transaction begins.
- STATEMENT_TIMESTAMP(): Retrieves the current DateTime with timezone information at which the current statement executes.
- TRANSACTION_TIMESTAMP(): It works the same way as the NOW() function.

Let’s comprehend how these functions work via the following example.

Example: How Do the TIMESTAMPTZ Functions Work in Postgres?

Let’s practice the functions mentioned above via the INSERT statement:

INSERT INTO employee_data()
VALUES(2, 'Joseph', NOW()),
(3, 'Henry', CURRENT_TIMESTAMP),
(4, 'Mike', CLOCK_TIMESTAMP()),
(5, 'Joseph', STATEMENT_TIMESTAMP())
(6, 'Joseph', TRANSACTION_TIMESTAMP());

Execute the “SELECT *” command to see the newly inserted data:

SELECT * FROM employee_data;

The TIMESTAMPTZ values have been inserted into the “employee_joining_date” via the built-in DateTime functions.

Conclusion

The PostgreSQL “TIMESTAMPTZ” or “TIMESTAMP With TIME ZONE” data type is used to store a timestamp value that includes the time zone information. Postgres offers several date-time functions to deal with temporal data. Some functions with the return type “TIMESTAMPTZ” include the “NOW()” function, STATEMENT_TIMESTAMP() function, CURRENT_TIMESTAMP function, etc. This blog post explained the working of TIMESTAMP with timezone data type using suitable examples.