PostgreSQL TIME Data Type

Summary: in this tutorial, you will learn about 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 the time data in the database.

Here’s the syntax for declaring a column with the TIME data type:

column_name TIME(precision);Code language: SQL (Structured Query Language) (sql)

In this syntax, the precision specifies the fractional seconds precision for the time value, which ranges from 1 to 6.

The TIME data type requires 8 bytes and its allowed range is from 00:00:00 to 24:00:00.

The following illustrates the common formats of the TIME values:

HH:MI   
HH:MI:SS
HHMISSCode language: SQL (Structured Query Language) (sql)

For example:

01:02
01:02:03
010203Code language: SQL (Structured Query Language) (sql)

To use a time value with the precision, you can utilize the following formats:

MI:SS.pppppp    
HH:MI:SS.pppppp
HHMISS.ppppppCode language: SQL (Structured Query Language) (sql)

In this syntax, p specifies the precision. For example:

04:59.999999
04:05:06.777777
040506.777777Code language: SQL (Structured Query Language) (sql)

PostgreSQL accepts almost any reasonable TIME format including SQL-compatible, ISO 8601, and so on.

PostgreSQL TIME data type example

In practice, you often use the TIME data type for the columns that store the time of day only such as the time of an event or a shift. For example:

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

CREATE TABLE shifts (
    id serial PRIMARY KEY,
    shift_name VARCHAR NOT NULL,
    start_at TIME NOT NULL,
    end_at TIME NOT NULL
);  Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the shifts table:

INSERT INTO shifts(shift_name, start_at, end_at)
VALUES('Morning', '08:00:00', '12:00:00'),
      ('Afternoon', '13:00:00', '17:00:00'),
      ('Night', '18:00:00', '22:00:00');Code language: SQL (Structured Query Language) (sql)

Third, query data from the shifts table:

SELECT * FROM shifts;Code language: SQL (Structured Query Language) (sql)

Output:

 id | shift_name | start_at |  end_at
----+------------+----------+----------
  1 | Morning    | 08:00:00 | 12:00:00
  2 | Afternoon  | 13:00:00 | 17:00:00
  3 | Night      | 18:00:00 | 22:00:00
(3 rows)

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 the time of day with time zone.

The following statement illustrates how to declare a column whose data type is TIME WITH TIME ZONE:

column TIME WITH TIME ZONECode language: SQL (Structured Query Language) (sql)

The storage size of the TIME WITH TIME ZONE data type is 12 bytes, allowing you to store a time value with the time zone that ranges from 00:00:00+1459 to 24:00:00-1459.

The following are some examples of the TIME WITH TIME ZONE type:

04:05:06 PST    
04:05:06.789-8   Code language: SQL (Structured Query Language) (sql)

When dealing with timezone, it is recommended to use TIMESTAMP instead of the TIME WITH TIME ZONE type. This is because the time zone has very little meaning unless it is associated with both date and time.

Handling PostgreSQL TIME values

Let’s explore some functions that handle time values.

1) Getting the current time

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

SELECT CURRENT_TIME;

Output:

timetz
--------------------
 00:51:02.746572-08
(1 row)Code language: SQL (Structured Query Language) (sql)

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

SELECT CURRENT_TIME(5);

Output:

   current_time
-------------------
 00:52:12.19515-08
(1 row)Code language: SQL (Structured Query Language) (sql)

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:

SELECT LOCALTIME;

Output:

      localtime
-----------------
 00:52:40.227186
(1 row)Code language: SQL (Structured Query Language) (sql)

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

SELECT LOCALTIME(0);

Output:

 localtime
----------
 00:56:08
(1 row)  Code language: SQL (Structured Query Language) (sql)

2) Converting time to a different time zone

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

[TIME with time zone] AT TIME ZONE time_zoneCode language: SQL (Structured Query Language) (sql)

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

SELECT LOCALTIME AT TIME ZONE 'UTC-7';Code language: JavaScript (javascript)

Output:

      timezone
--------------------
 16:02:38.902271+07
(1 row)Code language: SQL (Structured Query Language) (sql)

2) Extracting hours, minutes, and seconds from a time value

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

EXTRACT(field FROM time_value);Code language: SQL (Structured Query Language) (sql)

The field can be the hour, minute, second, or milliseconds. For example:

SELECT
    LOCALTIME,
    EXTRACT (HOUR FROM LOCALTIME) as hour,
    EXTRACT (MINUTE FROM LOCALTIME) as minute, 
    EXTRACT (SECOND FROM LOCALTIME) as second,
    EXTRACT (milliseconds FROM LOCALTIME) as milliseconds; Code language: SQL (Structured Query Language) (sql)

3) Arithmetic operations on time values

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

The following statement returns an interval between two times:

SELECT time '10:00' - time '02:00' AS result;Code language: PHP (php)

Output:

  result
----------
 08:00:00
(1 row)Code language: SQL (Structured Query Language) (sql)

The following statement adds 2 hours to the local time:

SELECT LOCALTIME + interval '2 hours' AS result;Code language: PHP (php)

Output:

    result
-----------------
 03:16:18.020418
(1 row)Code language: SQL (Structured Query Language) (sql)

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

Summary

Use the PostgreSQL TIME data type to store time data.

Was this tutorial helpful ?