TIME is one of the built-in data types in PostgreSQL that assists us in managing/storing the time values. It takes 8 bytes to store a time value. It stores the time in the standard format, i.e., “HH:MM:SS”. You can create a table column with TIME data type and store any time value between the range of “00:00:00” to “24:00:00”. To learn more about the usage of the TIME data type in PostgreSQL, we will walk you through different examples.

How to Use the TIME Data Type to Store Time Values in PostgreSQL

To use TIME data type in Postgres, first, create a table with a TIME-type column. Once created, you can insert any time value into that column using the INSERT command. Also, you can invoke different date-time functions on that column to store and manage time values.

Syntax

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

colName 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.

Example 3: How to Insert Current Time Into a Table

You can either insert the current time manually or invoke a built-in Postgres method to insert the current time into a table. Invoking a built-in method is a recommended approach, as shown in the following code:

INSERT INTO employee_attendence (emp_id, emp_check_in, emp_check_out)
 VALUES (6, '09:05:35', CURRENT_TIME);

In this example, we invoke the CURRENT_TIME method in the INSERT statement to store the current time in the “emp_check_out” column:

The output demonstrates that the current time has been inserted into the “emp_check_out” column. For more clarity, you can execute the SELECT query with the “*” wildcard as follows:

SELECT * FROM employee_attendence;

The output shows that the stated method inserts the time with precision. If you want to store the current time without precision, execute the INSERT query as follows:

INSERT INTO employee_attendence (emp_id, emp_check_in, emp_check_out)
 VALUES (7, '09:05:35', CURRENT_TIME(0));

You can cross-check the newly inserted record by executing the command:

SELECT * FROM employee_attendence;

Note: You can also employ the “LOCALTIME” method to insert/store local time into a Postgres table.

Example 4: How to Extract/Fetch Only Specific Time Field From a Table

If you need only a specific time field, instead of a complete time, you can use the EXTRACT() method with the desired field as follows:

SELECT EXTRACT (HOUR FROM emp_check_out) AS checkOut_hour,
 EXTRACT (MINUTE FROM emp_check_out) AS checkOut_min
 FROM employee_attendence;

In this code, we extract the hour and minute fields from the “emp_check_out” column, as shown below:

Example 5: How to Use Arithmetic Operators on Time Values

We can use different arithmetic operators (like +, -, and *) on TIME values to perform different tasks. For example, in the following code, we use the “-” operator to subtract “2” hours from the emp_check_out column:

SELECT emp_check_out, emp_check_out - TIME '02:00:00' As modified_time
 FROM employee_attendence;

We use the SELECT query to fetch the actual “emp_check_out” column and modified time for “emp_check_out” column:

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 values with the time zone. Follow the below-given syntax to create a time column with a 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:

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

To use TIME data type in Postgres, create a table column with the following syntax “colName TIME”. Once you succeed in creating a TIME column, you can insert and store any time value in that particular column. The TIME data type stores the time values without the time zone. However, you can use the "TIME WITH TIME ZONE" data type if you are interested in storing time values with time zones. The TIME data type takes 8 bytes to store a time value while the TIME WITH TIME ZONE data type takes 12 bytes to store the time values. Use any of the mentioned data types that perfectly fulfill your program’s requirements.