PostgreSQL TIME Data Type

Summary: in this tutorial, you’ll learn about the PostgreSQL TIME data type and how to use it to store time data in databases.

Getting Started with the PostgreSQL TIME data type #

In PostgreSQL, you use the TIME data type to store time data (without date) in tables.

Here’s the syntax for defining a TIME column:

CREATE TABLE table_name (
    column_name TIME,
    ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

A time column can store a time value from 00:00:00 to 24:00:00. PostgreSQL uses 8-byte to store a time value.

To set a default value for a TIME column, you use the DEFAULT constraint:

CREATE TABLE table_name(
    column_name TIME DEFAULT default_time,
    ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you want to set the current time as the default value, you can use the CURRENT_TIME function as follows:

CREATE TABLE table_name(
    column_name TIME DEFAULT CURRENT_TIME,
    ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Time Input #

PostgreSQL accepts various time formats, giving you flexibility. However, it is recommended to use ISO 8601, which is HH:MM:SS for time input, for example, 09:30:45

Time Output #

PostgreSQL allows you to set various time output formats like the time input formats. The default is the ISO 8601 format.

Time with precisions #

PostgreSQL allows you to store time with fractional seconds precision, up to 6 digits.

The following statement defines a TIME column with  fractional seconds precision:

CREATE TABLE table_name (
    column_name TIME(precision)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The valid value of precision is 1 to 6.

To use a time value with fractional seconds precision, you use the following format:

HH:MI:SS.pppppp

In this syntax, p determines the precision. For example, 09:30:45.123456.

Getting the current time #

To get the current time, you use the curent_time function:

SELECT current_time;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

    current_time
--------------------
 11:42:54.539691-07Code language: CSS (css)

The current_time returns the time with the timezone where the PostgreSQL is running and fractional second precision.

To remove the fractional second precision, you pass the precision as zero to the current_time function:

SELECT current_time(0);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 current_time
--------------
 11:43:07-07Code language: CSS (css)

To get the current local time, you use the localtime function:

SELECT localtime;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

    localtime
-----------------
 11:43:18.576448Code language: CSS (css)

And without fractional seconds:

SELECT localtime(0);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 localtime
-----------
 11:43:32Code language: CSS (css)

PostgreSQL TIME data type example #

The following example creates a table called schedules that includes a TIME column:

CREATE TABLE schedules (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  employee VARCHAR(100) NOT NULL,
  start_at TIME NOT NULL,
  end_at TIME NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The schedules table has two TIME columns, start and end, that represent the starting and ending times of the schedule.

Inserting time values #

The following example inserts a new row into the schedules table with an input time value:

INSERT INTO
  schedules (employee, start_at, end_at)
VALUES
  ('John Doe', '08:00:00', '12:00:00'),
  ('John Doe', '13:00:00', '17:00:00') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id | employee | start_at |  end_at
----+----------+----------+----------
  1 | John Doe | 08:00:00 | 12:00:00
  2 | John Doe | 13:00:00 | 17:00:00

In this example, we explicitly use time values in ISO 8601 format 'hh:mm:ss'.

Extracting hour, minute, and second from a time value #

The extract() function extracts a field that can be an hour, minute, and second from a time value.

For example, the following statement uses the extract() function to extract hour, minute, and second from the start_at column:

SELECT
  employee,
  start_at,
  extract(hour  from start_at) h,
  extract(minute from start_at) m,
  extract(second from start_at) s
FROM
  schedules;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 employee | start_at | h  | m |    s
----------+----------+----+---+----------
 John Doe | 08:00:00 |  8 | 0 | 0.000000
 John Doe | 13:00:00 | 13 | 0 | 0.000000

The second includes the fraction part. To remove it, you can cast the seconds to integers using the cast operator ::

SELECT
  employee,
  start_at,
  extract(hour  from start_at) h,
  extract(minute from start_at) m,
  extract(second from start_at)::INT s
FROM
  schedules;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 employee | start_at | h  | m | s
----------+----------+----+---+---
 John Doe | 08:00:00 |  8 | 0 | 0
 John Doe | 13:00:00 | 13 | 0 | 0Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use PostgreSQL TIME data type to store time values in a table.
• Use the extract() function to extract hour, minute, and second from a time value.

Quiz #