Introduction to PostgreSQL TIME data type

PostgreSQL provides theTIME data type that allows you to store the time data in the database.

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

column_name TIME(precision);

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

TheTIME data type requires 8 bytes and its allowed range is from00:00:00 to24:00:00.

The following illustrates the common formats of theTIME values:

HH:MIHH:MI:SSHHMISS

For example:

01:0201:02:03010203

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

MI:SS.ppppppHH:MI:SS.ppppppHHMISS.pppppp

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

04:59.99999904:05:06.777777040506.777777

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

PostgreSQL TIME data type example

In practice, you often use theTIME 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 namedshifts by using the following CREATE TABLE statement:

CREATE TABLE shifts (    idserial PRIMARY KEY,    shift_nameVARCHAR NOT NULL,    start_atTIME NOT NULL,    end_atTIME NOT NULL);

Second,insert some rows into theshifts 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');

Third,query data from theshifts table:

SELECT * FROM shifts;

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 theTIME data type, PostgreSQL provides theTIME 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 isTIME WITH TIME ZONE:

column TIME WITH TIME ZONE

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

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

04:05:06 PST04:05:06.789-8

When dealing with timezone, it is recommended to useTIMESTAMP instead of theTIME 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 theCURRENT_TIME function as follows:

SELECT CURRENT_TIME;

Output:

timetz-------------------- 00:51:02.746572-08(1 row)

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

SELECT CURRENT_TIME(5);

Output:

current_time------------------- 00:52:12.19515-08(1 row)

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

To get the local time, you use theLOCALTIME function:

SELECT LOCALTIME;

Output:

localtime----------------- 00:52:40.227186(1 row)

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

SELECT LOCALTIME(0);

Output:

localtime---------- 00:56:08(1 row)

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_zone

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

SELECT LOCALTIMEAT TIME ZONE 'UTC-7';

Output:

timezone-------------------- 16:02:38.902271+07(1 row)

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

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

EXTRACT(fieldFROM time_value);

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 (millisecondsFROM LOCALTIME)as milliseconds;

PostgreSQL TIME example

3) Arithmetic operations on time values

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

The following statement returns aninterval between two times:

SELECT time '10:00' - time '02:00' AS result;

Output:

result---------- 08:00:00(1 row)

The following statement adds 2 hours to the local time:

SELECT LOCALTIME+ interval'2 hours' AS result;

Output:

result----------------- 03:16:18.020418(1 row)

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

Summary

• Use the PostgreSQLTIME data type to store time data.

Last updated onFebruary 2, 2024

On this page

• Introduction to PostgreSQL TIME data type
• PostgreSQL TIME data type example
• PostgreSQL TIME WITH TIME ZONE type
• Handling PostgreSQL TIME values
• Summary