The time data type

This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

Values whose data type is time represent the time-of-day component of some moment (the hours and minutes as integer values and the seconds as a real number value with microsecond precision) in the "local" (a.k.a. "wall-clock") regime. Time values know nothing about timezones, and represent the time of day at some unspecified location. You can picture a time value as the number of microseconds from the exact midnight that starts the day. And because time values know nothing about timezones, they know nothing about Daylight Savings regimes, either: every day ought to run from midnight (inclusive) through the next midnight (exclusive). But notice this quirk:

select
  ('00:00:00.000000'::time)::text as "starting midnight",
  ('23:59:59.999999'::time)::text as "just before ending midnight",
  ('24:00:00.000000'::time)::text as "ending midnight";

This is the result:

 starting midnight | just before ending midnight | ending midnight
-------------------+-----------------------------+-----------------
 00:00:00          | 23:59:59.999999             | 24:00:00

The fact that this is allowed is a minor annoyance. The two times test as unequal"

select (
  '00:00:00.000000'::time =
  '24:00:00.000000'::time
  )::text;

The result is false. But because the time value carries no date information, you'd need to define and advertise auxiliary application semantics to interpret 24:00:00 as the end of a particular day rather than the start of the day that immediately follows the previous day.

Notice that subtracting one time value from another time value uses clock-time-semantics. Try this:

select
  ('02:00:00'::time - '01:00:00'::time)::text as "interval 1",
  ('24:00:00'::time - '00:00:00'::time)::text as "interval 2",
  justify_hours('24:00:00'::time - '00:00:00'::time)::text as "interval 3";

This is the result:

 interval 1 | interval 2 | interval 3
------------+------------+------------
 01:00:00   | 24:00:00   | 1 day

See the interval data type section. This section explains the mental model that allows 24 hours to be different (in a subtle way) from 1 day.

Here's another quirk: adding an interval value to a date value can cause silent wrap-around. Try this:

select '24:00:00'::time + '1 second'::interval;

This is the result:

 00:00:01

This suggests that you might get the same result from this attempt:

select '24:00:01'::time;

But the attempt causes this error:

22008: date/time field value out of range: "24:00:01"

Compare these quirky outcomes with those of corresponding tests that use plain timestamp values. See the section The plain timestamp and timestamptz data types. You should think very carefully about your reasons to prefer the time data type over the plain timestamp data type in application code and describe these reasons in your application's functional specification document.