Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Dealing with dates
Working with time
12. The TIME data type
Date and time data types in PostgreSQL
Extracting dates and times
Timezone conversion
Format date and time
Current date and time data
Summary

Instruction

PostgreSQL uses the time data type to store time data. Its range is '00:00:00' to '24:00:00' and for time with time zone from '00:00:00+1459' to '24:00:00-1459'. Time in PostgreSQL can contain also the number of fractional seconds. As you saw, the format for this data type is:

HH:MM:SS.n

Breaking this down, we see that:

  • HH stands for hours.
  • MM stands for minutes. Each minute is represented by two digits from 00 to 59.
  • SS stands for seconds. Each second is represented by two digits from 00 to 59.
  • .n stands for the number of fractional seconds. This optional value can range from 0 to 6 digits. By default, its value is 0.

So we have the time data type. Notice that time uses a 24-hour clock, so write 14:25 for 2:25 PM.

The precision of time depends on the database designer; in our database, we use the default zero precision.

Specifying a value in a column of time type requires you to put the value in single quotes:

SELECT
  code,
  departure_time
FROM route
WHERE arrival_time = '20:15:00';

Exercise

Show the code for the PerfectAir route arriving at 9:30 AM.

Stuck? Here's a hint!

Don't use "AM" – databases use 24 hours clock 😉