PostgreSQL: TIME Data Type

PostgreSQL supports TIME datatype to store the time values of a day.

The TIME datatype takes 8 bytes of storage. The TIME datatype can store up to 6 digits of precision to define a number of fractional digits placed in the second's field. The range for TIME datatype is from 00:00:00 to 24:00:00.

<column_name> TIME(<precision>)

The followings are different TIME format examples:

HH:MM		-> 07:10
HH:MM:SS	-> 07:10:34
HHMMSS	    -> 071034

The TIME formats can be used with precision(p) as shown below:

MM:SS.pppppp		-> 11:34.666666
HH:MM:SS.pppppp	    -> 06:11:34.888888
HHMMSS.pppppp	    -> 061134.888888

Let's create a table Work_Shift with a column of the TIME type, as shown below.

CREATE TABLE Work_shift (  
    id serial PRIMARY KEY,
    shift_name VARCHAR NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL
);

When you create a column with datatype as TIME, by default it will create a column as TIME without a time zone.

The following inserts data into a TIME column.

INSERT INTO Work_shift(shift_name, start_time, end_time)
VALUES
	('First Shift', '09:00:00', '06:00:00'),
	('Second Shift', '12:00:00', '21:00:00'),
	('Third Shift', '14:00:00', '23:00:00');

Let's check the data we inserted into Work_Shift table.

TIME Type with Time Zone

PostgreSQL allows us to define TIME with time zone type. The time zone can be specified as a full-time zone name like America/New_York or as time zone abbreviations like IST, PST etc.

The TIME with time zone takes 12 bytes of storage to store data. Some example are below.

06:55:34 IST
16:10:54 PST

PostgreSQL Time functions

Here are some Postgres provided Time functions.

CURRENT_TIME()

To get the current time with the time zone, use the CURRENT_TIME function. To get the current time with specific precision and with time zone use CURRENT_TIME(precision) function.

SELECT CURRENT_TIME, CURRENT_TIME(6);

LOCALTIME()

To get local time, use the LOCALTIME function. To get local time with specific precision use the LOCALTIME(precision) function.

SELECT LOCALTIME, LOCALTIME(5);

Extracting Hours, Minutes, Seconds from Time Value

To extract hours, minutes, and seconds from time value, use the EXTRACT functions as below.

SELECT
    CURRENT_TIME,
    EXTRACT (HOUR FROM CURRENT_TIME) as hour,
    EXTRACT (MINUTE FROM CURRENT_TIME) as minute, 
    EXTRACT (SECOND FROM CURRENT_TIME) as second,
    EXTRACT (milliseconds FROM CURRENT_TIME) as milliseconds;

Convert Time to Different Time Zones

Time can be converted to another time zone using the PostgreSQL functions.

The following converts a local time to the Eastern Standard Time (EST) using the following statement:

SELECT LOCALTIME, CURRENT_TIME AT TIME ZONE 'EST';

Performing Arithmetic Operations on Time

PostgreSQL allows you to perform arithmetic operations like +, -, *, and / on time values and interval values.

In the following query result column adds 6 hours to localtime.

SELECT LOCALTIME, LOCALTIME + interval '6 hours' AS result;

The following result column shows interval between LOCALTIME and TIME values.

SELECT LOCALTIME, LOCALTIME - time '02:00' AS result;