PostgreSQL TIME Data Type

PostgreSQL supports TIME data type to store time values.

PostgreSQL TIME data type stores values ​​using 8 bytes. TIME The allowed range for the data type is from 00:00:00to 24:00:00.

The TIME values is stored in the following format

HH:MI:SS.ssssss

Here:

• HH indicates hour value.
• MI indicates minute value.
• SS indicates second value.
• ssssss indicates fractional seconds value.

PostgreSQL TIME syntax

To create a column of TIME data type, use the following syntax:

column_name TIME column_constraint

To insert an actual value into a time column, you can use the following format:

• HH:MI:SS[.ssssss]
• HHMISS[.ssssss]
• MI:SS[.ssssss]
• HH:MI

You can use type conversion to convert a text-formatted time value to a time type, as follows:

'2022-08-30'::TIME

PostgreSQL TIME Examples

Create a new table named test_time:

CREATE TABLE test_time (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  time_value TIME NOT NULL DEFAULT current_time
);

The test_time table consists of two columns:

1. The id column is the primary key column that identifies each rows, and it is an identity column.
2. The time_value column is of TIME data type, it cannot be null, and has the current time as the default value.

Insert TIME data

To insert a time value into a TIME column, you must ensure that the data is in the correct format. The following INSERT statement test_time inserts several new rows into the table.

INSERT INTO test_time (time_value)
VALUES
  ('10:10:10'),
  ('10:10:11'),
  ('10:10:12'),
  ('10:10'),
  ('101010'),
  ('10:10:10.111111')
RETURNING *;

 id |   time_value
----+-----------------
  1 | 10:10:10
  2 | 10:10:11
  3 | 10:10:12
  4 | 10:10:00
  5 | 10:10:10
  6 | 10:10:10.111111
(6 rows)

Update TIME data

To update TIME columns, use this UPDATE statement and pass in a properly formatted value:

UPDATE test_time
SET time_value = '10:10:09'
WHERE id = 1
RETURNING *;

 id | time_value
----+------------
  1 | 10:10:09
(1 row)

Use TIME column in WHERE conditions

You can use the TIME column to filter rows in the WHERE clause.

To find the rows whose time_value is 10:10:10, use the following statement:

SELECT *
FROM test_time
WHERE time_value = '10:10:10';

 id | time_value
----+------------
  5 | 10:10:10
(1 row)

You can also find all rows with time_value greater that 10:10:10, as follows:

SELECT *
FROM test_time
WHERE time_value > '10:10:10';

 id |   time_value
----+-----------------
  2 | 10:10:11
  3 | 10:10:12
  6 | 10:10:10.111111
(3 rows)

Format the time value to the specified format

PostgreSQL provides TO_CHAR() function to format time values to a specified format. The TO_CHAR() function accepts two parameters, the first parameter is the time value to format, and the second parameter is the format template.

To display the time in the yyyy/mm/dd format, use the following statement:

SELECT
  id,
  to_char(time_value, 'HHMISS')
FROM
  test_time;

 id | to_char
----+---------
  2 | 101011
  3 | 101012
  4 | 101000
  5 | 101010
  1 | 101009
(5 rows)

PostgreSQL time functions

PostgreSQL provides many time-related functions.

To get the current time, use current_time:

SELECT current_time;

   current_time
-------------------
 17:18:49.94353+08

You can specify the fractional second precision of the time using current_time, as follows:

SELECT current_time(1);

 current_time
---------------
 17:19:00.3+08

To get the hours, minutes, and seconds parts of a time value, use the date_part() function :

SELECT
  date_part('hour', '10:11:12'::time) "hour",
  date_part('minute', '10:11:12'::time) "minute",
  date_part('second', '10:11:12'::time) "second";

 hour | minute | second
------+--------+--------
   10 |     11 |     12

Conclusion

This article discusses the PostgreSQL TIME data type, as well as some commonly used TIME operators and functions.