A Quick Guide To The PostgreSQL TIME Data Type

Summary: this tutorial introduces you to the PostgreSQL TIME data types and some handy functions to handle time values.

Introduction to PostgreSQL TIME data type

PostgreSQL provides the TIME data type that allows you to store and manipulate the time of day values.

The following statement illustrates how to declare a column with the TIME data type:

A time value may have a precision with up to 6 digits. The precision specifies the number of factional digits placed in the second field.

The TIME data type requires 8 bytes storage size and its allowed range is from 00:00:00 to 24:00:00. The following illustrates the common formats of the TIME values:

For example:

If you want to use the precision, you can use the following formats:

In this form, p is the precision. For example:

PostgreSQL actually accepts almost any reasonable TIME format including SQL-compatible, ISO 8601, etc.

PostgreSQL TIME example

We often use the TIME data type for the columns that store time of day only e.g., the time of an event or a shift. Consider the following example.

First, create a new table named shifts by using the CREATE TABLE statement:

Second, insert some rows into the shifts table:

Third, query data from the shifts table:

PostgreSQL TIME with time zone type

Besides the TIME data type, PostgreSQL provides the TIME with time zone data type that allows you to store and manipulate time of day with time zone.

The following statement illustrates how to declare a column whose data type is TIME with time zone:

The storage size of the TIME with time zone data type is 12 bytes that allow you store a time value with time zone in the range from 00:00:00+1459 to 24:00:00-1459.

The following illustrates the TIME with time zone values:

Handling PostgreSQL TIME values

Getting the current time

To get the current time with time zone, you use the CURRENT_TIME function as follows:

To get current time with a specific precision, you use the CURRENT_TIME(precision) function:

Notice that without specifying the precision, the CURRENT_TIME function returns a time value with the full available precision.

To get the local time, you use the LOCALTIME function:

Similarly, to get the local time with a specific precision, you use the LOCALTIME(precision) function:

Converting time to a different timezone

To convert time to an different time zone, you use the following form:

For example, to convert the local time to the time at the time zone UTC-7, you use the following statement:

Extracting hours, minutes, seconds from a time value

To extracting hours, minutes, seconds from a time value, you use the EXTRACT function as follows:

The field can be hour, minute, second, milliseconds as shown in the following example:

Arithmetic operations on time values

PostgreSQL allows you to apply arithmetic operators such as +, -, * , etc.,  on time values and between time and interval values.

The following statement returns an interval between two time values:

The following statement adds 2 hours to the local time:

In this example, the sum of a time value and an interval value is a time value.

In this tutorial, you have learned about the PostgreSQL TIME data type and how to handle time values using time related functions.