PostgreSQL – TIME Data Type

PostgreSQL provides user with TIME data type that is used to handle time values. It requires 8 bytes of storage and can have precision up to 6 digits. It can range from 00:00:00 to 24:00:00.

Syntax: column_name TIME(precision);

The common TIME formats are illustrated below:

HH:MM   
HH:MM:SS
HHMMSS

If precision is required the following format needs to be followed:

MM:SS.pppppp    
HH:MM:SS.pppppp
HHMMSS.pppppp

For instance below are sample TIME value formats:

Without precision:
01:02
01:02:03
010203

With precision:
04:59.999999
04:05:06.777777
040506.777777

Now let’s look into some examples for better understanding.
Example:
In this example we will create a table that holds team schedules details of a company. First, create a new table named team_schedule by using the following commands:

CREATE TABLE team_schedule (
    id serial PRIMARY KEY,
    team_name VARCHAR NOT NULL,
    clock_in_time TIME NOT NULL,
    clock_out_time TIME NOT NULL
);

Now, insert some rows into the team_schedule table:

INSERT INTO team_schedule(team_name, clock_in_time, clock_out_time)
VALUES('Marketing', '09:00:00', '18:00:00'),
      ('Sales', '10:00:00', '19:00:00'),
      ('Mentors', '09:00:00', '18:00:00'),
      ('Software', '11:00:00', '20:00:00'),
      ('Content', '10:00:00', '19:00:00');

Finally, we query from the shifts table using the below command:

SELECT
    *
FROM
    team_schedule;

Output:

My Personal Notes arrow_drop_up

Save