Menu

PostgreSQL timetz: Time of Day with a UTC Offset

Updated on

PostgreSQL timetz is an abbreviation for time with time zone. It stores a time of day and a UTC offset, but no date. The stored offset is returned as stored; it is not converted using the session time zone. Because the date is missing, the value cannot identify a unique instant or determine which daylight-saving offset applies to a named region. PostgreSQL notes that time with time zone has questionable usefulness for typical applications and is mainly supported for SQL-standard compliance and legacy use. See the PostgreSQL date/time type reference.

Syntax and example

TIME WITH TIME ZONE

timetz is accepted as a PostgreSQL abbreviation. A literal can include a numeric UTC offset:

SELECT TIME WITH TIME ZONE '14:30:00-05:00' AS meeting_time;

The returned value contains the time and offset. It does not say that this is an appointment in America/New_York: the offset alone has no date and no future daylight-saving rules.

Choose a type for the actual meaning

  • Use time without time zone for a recurring wall-clock time when no offset is part of the value.
  • Use timestamptz for a specific instant that should be converted for display in different session time zones.
  • For a future local appointment tied to a region, store its date and local time together with an IANA zone name such as America/New_York, then apply the zone’s rules when converting. A timetz offset cannot represent those rules by itself.

PostgreSQL’s time-zone guidance recommends date/time types that include both date and time for most time-zone use cases.