{"id":749,"date":"2024-12-08T11:47:19","date_gmt":"2024-12-08T04:47:19","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=749"},"modified":"2025-01-02T13:55:06","modified_gmt":"2025-01-02T06:55:06","slug":"postgresql-time","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-time\/","title":{"rendered":"PostgreSQL TIME Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn about the PostgreSQL TIME<\/code> data type and how to use it to store time data in databases.<\/p>\n\n\n\n

Getting Started with the PostgreSQL TIME data type #<\/a><\/h2>\n\n\n\n

In PostgreSQL, you use the TIME<\/code> data type to store time data (without date<\/a>) in tables.<\/p>\n\n\n\n

Here’s the syntax for defining a TIME<\/code> column:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name<\/span> (\n    column_name<\/span> TIME<\/span>,\n    ...\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
A time column can store a time value from 00:00:00<\/code> to 24:00:00<\/code>. PostgreSQL uses 8-byte to store a time value.<\/p>\n\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span>(\n    column_name<\/span> TIME<\/span> DEFAULT<\/span> default_time,\n    ...\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to set the current time as the default value, you can use the CURRENT_TIME<\/code> function as follows:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span>(\n    column_name<\/span> TIME<\/span> DEFAULT<\/span> CURRENT_TIME<\/span>,\n    ...\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Time Input #<\/a><\/h3>\n\n\n\n
PostgreSQL accepts various time formats, giving you flexibility. However, it is recommended to use ISO 8601, which is HH:MM:SS<\/code> for time input, for example, 09:30:45<\/code><\/p>\n\n\n\n
Time Output #<\/a><\/h3>\n\n\n\n
PostgreSQL allows you to set various time output formats like the time input formats. The default is the ISO 8601 format.<\/p>\n\n\n\n
Time with precisions #<\/a><\/h2>\n\n\n\n
PostgreSQL allows you to store time with fractional seconds precision, up to 6 digits.<\/p>\n\n\n\n
The following statement defines a TIME<\/code> column with  fractional seconds precision:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span> (\n    column_name<\/span> TIME<\/span>(precision<\/span>)\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The valid value of precision is 1 to 6.<\/p>\n\n\n\n
To use a time value with fractional seconds precision, you use the following format:<\/p>\n\n\n\n
HH:MI:SS.pppppp<\/code><\/p>\n\n\n\n
In this syntax, p<\/code> determines the precision. For example, 09:30:45.123456<\/code>.<\/p>\n\n\n\n
Getting the current time #<\/a><\/h2>\n\n\n\n
To get the current time, you use the curent_time<\/code> function:<\/p>\n\n\n
SELECT<\/span> current_time<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
    current_time<\/span>\n--------------------<\/span>\n 11:42<\/span>:54.539691-07<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
The current_time<\/code> returns the time with the timezone where the PostgreSQL is running and fractional second precision.<\/p>\n\n\n\n
To remove the fractional second precision, you pass the precision as zero to the current_time<\/code> function:<\/p>\n\n\n
SELECT<\/span> current_time<\/span>(0<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 current_time<\/span>\n--------------<\/span>\n 11:43<\/span>:07-07<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
To get the current local time, you use the localtime<\/code> function:<\/p>\n\n\n
SELECT<\/span> localtime<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
    localtime<\/span>\n-----------------<\/span>\n 11:43<\/span>:18.576448<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
And without fractional seconds:<\/p>\n\n\n
SELECT<\/span> localtime<\/span>(0<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 localtime<\/span>\n-----------<\/span>\n 11:43<\/span>:32<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL TIME data type example #<\/a><\/h2>\n\n\n\n
The following example creates a table<\/a> called schedules<\/code> that includes a TIME<\/code> column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> schedules (\n  id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  employee VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n  start_at TIME<\/span> NOT<\/span> NULL<\/span>,\n  end_at TIME<\/span> NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The schedules table has two TIME<\/code> columns, start and end, that represent the starting and ending times of the schedule.<\/p>\n\n\n\n
Inserting time values #<\/a><\/h2>\n\n\n\n
The following example inserts a new row into the schedules table with an input time value:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  schedules (employee, start_at, end_at)\nVALUES<\/span>\n  ('John Doe'<\/span>, '08:00:00'<\/span>, '12:00:00'<\/span>),\n  ('John Doe'<\/span>, '13:00:00'<\/span>, '17:00:00'<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 id | employee | start_at |  end_at\n----+----------+----------+----------\n  1 | John Doe | 08:00:00 | 12:00:00\n  2 | John Doe | 13:00:00 | 17:00:00<\/code><\/span><\/pre>\n\n\n
In this example, we explicitly use time values in ISO 8601 format 'hh:mm:ss'<\/code>.<\/p>\n\n\n\n
Extracting hour, minute, and second from a time value #<\/a><\/h2>\n\n\n\n
The extract()<\/code> function extracts a field that can be an hour, minute, and second from a time value.<\/p>\n\n\n\n
For example, the following statement uses the extract()<\/code> function to extract hour, minute, and second from the start_at<\/code> column:<\/p>\n\n\n
SELECT<\/span>\n  employee,\n  start_at,\n  extract(hour<\/span>  from<\/span> start_at) h,\n  extract(minute<\/span> from<\/span> start_at) m,\n  extract(second<\/span> from<\/span> start_at) s\nFROM<\/span>\n  schedules;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 employee | start_at | h  | m |    s\n----------+----------+----+---+----------\n John Doe | 08:00:00 |  8 | 0 | 0.000000\n John Doe | 13:00:00 | 13 | 0 | 0.000000<\/code><\/span><\/pre>\n\n\n
The second includes the fraction part. To remove it, you can cast <\/em>the seconds to integers using the cast operator ::<\/code><\/p>\n\n\n
SELECT<\/span>\n  employee,\n  start_at,\n  extract(hour<\/span>  from<\/span> start_at) h,\n  extract(minute<\/span> from<\/span> start_at) m,\n  extract(second<\/span> from<\/span> start_at)::INT<\/span> s\nFROM<\/span>\n  schedules;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 employee | start_at | h  | m | s\n----------+----------+----+---+---<\/span>\n John Doe | 08<\/span>:00<\/span>:00<\/span> |  8<\/span> | 0<\/span> | 0<\/span>\n John Doe | 13<\/span>:00<\/span>:00<\/span> | 13<\/span> | 0<\/span> | 0<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
To set a default value for a TIME<\/code> column, you use the DEFAULT<\/a><\/code> constraint:<\/p>\n\n\n