PostgreSQL offers a TIME data type that assists us in handling the time values. The TIME data type takes 8 bytes to store a time value. Postgres allows us to store the time value between the range of 00:00:00 to 24:00:00.

So, let’s learn how to use the TIME data type in PostgreSQL using some suitable examples.

How to Use The TIME Data Type to Store Time Values in PostgreSQL?

Let’s look at the syntax below to learn how to create/declare a column with TIME data type:

col_name TIME(prc);

Here, “prc” represents precision. PostgreSQL allows us to store a time value with or without precision. A time value can have a precision of up to six digits.

Some commonly used TIME formats without precision are listed below:

- HH:MI:SS, for example, 10:24:30.

- HHMISS, for example, 102430.

- HH:MI, for example, 1024.

The frequently used time formats with precision are illustrated below:

- HH:MI:SS.pppppp, for example, 10:24:30.999999

- HHMISS.pppppp, for example, 102430.999999

- MI:SS.pppppp, for example, 24:30.999999

Here, we mentioned some commonly used time formats; however PostgreSQL supports almost all the valid time formats including ISO 8601 formats, SQL-compatible formats and so on.

Example #1: How to Create a Column With TIME Data Type in PostgreSQL?

Let’s create a table named “employee_attendence” with three columns: emp_id, emp_check_in, and emp_check_out:

CREATE TABLE employee_attendence( 
  emp_id INT PRIMARY KEY, 
  emp_check_in TIME NOT NULL, 
  emp_check_out TIME NOT NULL 
   );

The output shows that the employee_attendence table has been created successfully. Let’s verify the column names and their respective data types using the SELECT command:

SELECT * FROM employee_attendence;

The output authenticates that the employee_attendence table has been created successfully.

Example #2: How to Insert Time Values Into a table?

Let’s insert the employee’s attendance record into the employee_attendence table:

INSERT INTO employee_attendence (emp_id, emp_check_in, emp_check_out)
 VALUES (5, '09:05:35', '05:15:00'),
   (1, '09:00:45', '05:15:00'),
   (4, '09:25:55', '06:00:14'),
   (3, '09:15:25', '05:35:00'),
   (2, '09:02:15', '05:00:20');

In this example, we inserted time values in “HH:MM:SS” format. Here is what we will get on successfully executing the INSERT query:

The output shows that five records have been inserted into the employee_attendence table. Let’s describe the table’s records using the SELECT command:

SELECT * FROM employee_attendence;

The output proved that the TIME data type successfully stored the time values without a time zone.

How to Get Time Values With Time Zones in PostgreSQL?

The previous example shows that the TIME data type stores the time values without the time zone. Use the “TIME WITH TIME ZONE” data type to store the time values with the time zone. The TIME WITH TIME ZONE data type takes 12 bytes to store the time of the day with the time zone. Follow the below-given syntax to store the time values with time zone:

col_name TIME WITH TIME ZONE;

Let’s consider the following example to learn how the “TIME WITH TIME ZONE” data type works in PostgreSQL.

Example #1: How to Create a Column Using “TIME WITH TIME ZONE” Data Type in PostgreSQL?

Let’s create a table emp_attendence that contains some columns of type “TIME WITH TIME ZONE”:

CREATE TABLE emp_attendence (
   emp_id INT PRIMARY KEY,
   emp_check_in TIME WITH TIME ZONE NOT NULL,
   emp_check_out TIME WITH TIME ZONE NOT NULL 
   );

Let’s execute the SELECT command to see the table’s structure:

SELECT * FROM emp_attendence;

The output shows that three columns with the respective data types have been created successfully.

Example #2: How to Insert Time Values With Time Zone Into a table?

Let’s insert some time values along with the time zone to the emp_attendence table:

INSERT INTO emp_attendence (emp_id, emp_check_in, emp_check_out)
 VALUES (3, '09:00:00   BST', '05:00:00   BST'),
   (1, '09:05:45 BST', '05:05:45 BST'),
   (2, '09:12:15 BST', '05:12:15 BST');

In this example, we stored the time along with the BST (British Summer Time) time zone. Let’s execute the SELECT statement to describe the table details:

SELECT * FROM emp_attendence;

This is how the “TIME WITH TIME ZONE” data type works in PostgreSQL. That was all the necessary information regarding the TIME data type in PostgreSQL.

Conclusion

In PostgreSQL, the TIME data type is used to handle the time values. The TIME data type stores the time values without the time zone. The "TIME WITH TIME ZONE" data type stores time values with time zones. The TIME data type takes 8 bytes to store a time value; however, the TIME WITH TIME ZONE data type takes 12 bytes to store the time values. This write-up taught us how to use the TIME data type in Postgres.