PostgreSQL TIME Data Type

This article describes how to use the TIME type in PostgreSQL and some useful functions for working with TIME values.

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 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:


PostgreSQL TIME Examples

Create a new table named test_time:

CREATE TABLE test_time (
  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)
 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
 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:

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:

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:

  to_char(time_value, 'HHMISS')
 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;

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

SELECT current_time(1);

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

  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


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