PostgreSQL offers many time and date data types to store this kind of data in the required and accurate format. There are several variants of DateTime data types that PostgreSQL provides. Sometimes we store the time as a simple time, sometimes we need to store it as a timestamp, sometimes it has to be the time with a time zone, and sometimes without a time zone. This article revolves around a data type that stores time with time zones. This type is the TIMETZ data type.

How to Use TIMETZ (Time With Time Zone) Data Type in PostgreSQL?

Users can store information about time along with the time zone using the TIMETZ data type in PostgreSQL. The format of this data type works similarly to the time data type but additionally, the TIMETZ also specifies the time zone. The basic syntax for this data type is:

name_of_time_variable TIMETZ;

The data type declared with this data type will store the time along with the zone information.

Let’s see how to use the TIMETZ data types in PostgreSQL.

Example: How To Use the Timetz Data Types In PostgreSQL

Let’s construct a time named “job_timings” containing the schedule of duty timing of different persons working in a company at different times. The query can be written as:

CREATE TABLE job_timings (  
  id SERIAL PRIMARY KEY,
  job_start_time TIMETZ NOT NULL,
  job_timezone TEXT NOT NULL
   );

We have defined the “job_start_time” as the TIMETZ data type. After the successful creation of the table, we will be inserting some data into the table using the following query:

INSERT INTO job_timings(job_start_time, job_timezone) VALUES
   ('07:30:00-05:00', 'America/Los_Angeles'),
   ('10:00:00+01:00', 'Europe/Paris'),
   ('06:00:00+08:00', 'Asia/Shanghai');

Executing the above query will successfully insert the values in the table which can be verified by following output:

Now, if we select the table to see the values, we will run the following query:

SELECT * FROM job_timings;

The output is:

In the above output, you can note the data type of the “job_start_time” is time with time zone because it was defined as TIMETZ data type.

So this is how the TIMETZ data type can be used in Postgres.

Conclusion

The TIMETZ data type stores the time information with the timezone. This data type is used to store the data where the timezone is considered to be important. In this article, we have seen why the TIMETZ data type is used and how is it used in PostgreSQL.