PostgreSQL TIME Data Type

PostgreSQL TIME (equivalent to TIME WITHOUT TIME ZONE) stores a time of day without a date or time-zone offset. Its range is 00:00:00 through 24:00:00, with one-microsecond resolution.

PostgreSQL also supports TIME WITH TIME ZONE, but its offset has no date to determine daylight-saving rules. PostgreSQL recommends using date and timestamp types for most time-zone-aware applications.

PostgreSQL TIME syntax

The time-of-day types are TIME[(p)] [WITHOUT TIME ZONE] and TIME[(p)] WITH TIME ZONE; TIME alone means TIME WITHOUT TIME ZONE. The optional precision p ranges from 0 to 6 fractional-second digits.

Valid time-of-day inputs include HH:MI, HH:MI:SS, and HH:MI:SS.ffffff. For example, cast a valid time literal as follows:

SELECT '10:10:10.123456'::TIME;

For local date/time input formats and time-zone parsing, see PostgreSQL’s date/time type documentation.

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 localtime
);

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 stores the current session-local time of day without a time-zone offset, using LOCALTIME as its default.

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 a TIME value

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 a TIME value with hours, minutes, seconds, and microseconds, use the HH24:MI:SS.US format template:

SELECT
  id,
  to_char(time_value, 'HH24:MI:SS.US')
FROM
  test_time
ORDER BY id;

 id |      to_char
----+-------------------
  1 | 10:10:09.000000
  2 | 10:10:11.000000
  3 | 10:10:12.000000
  4 | 10:10:00.000000
  5 | 10:10:10.000000
  6 | 10:10:10.111111
(6 rows)

PostgreSQL time functions

PostgreSQL provides many time-related functions.

CURRENT_TIME returns TIME WITH TIME ZONE, while LOCALTIME returns TIME WITHOUT TIME ZONE. Use the former if the current offset is needed, or the latter to populate a TIME value without an offset. PostgreSQL does not recommend TIME WITH TIME ZONE for most applications because it lacks a date for daylight-saving rules. See the official date/time type guidance.

SELECT current_time, localtime;

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

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.