Try a preview of AI-powered search below!

The following subsections provide the conceptual background for the accounts of the date-time data types that the table shown in the Synopsis lists and for the operations that the date-time operators and built-in SQL functions perform.

• Absolute time and the UTC Time Standard
• Wall-clock-time and local time
• Timezones and the offset from the UTC Time Standard
• The strange history of timezones
• Two ways of conceiving of time: calendar-time and clock-time
  • Calendar-time
  • Clock-time
  • Practical examples

In particular, the distinction between calendar-time and clock-time is crucial to understanding the tricky topic of interval arithmetic.

Absolute time and the UTC Time Standard

Ignoring what Einstein had to say, the absolute time is identical at all spots on Planet Earth at every moment as time flows from the indefinite past, through the present, and on into the indefinite future.

These days, it's easy to find a visual image for the notion of absolute time. Imagine a dedicated TV channel, available everywhere on the planet, that simply broadcasts what a camera sees that's pointing at a clock located in Greenwich UK. The clock is set to what a sundial, standing on the exact zero degree meridian, will read—i.e. it will read 12:00:00 noon when the sundial casts its shortest shadow.

Absolute time can now be measured with very high precision and accuracy with an atomic clock. Seconds, minutes, and hours can all be defined as specific multiples of the caesium standard unit.

Of course, there is a standard for absolute time: the UTC Time Standard.

Naturally, this "TV channel" (continuing with this metaphor) is available as a web service—for example, the World Time API. Try this URL

http://worldtimeapi.org/api/timezone/Etc/UTC/

It returns a JSON document that includes the current absolute time in this ISO 8601 format:

2021-05-16T22:58:53.122521+00:00

This is usable, "as is", as a SQL literal—exploiting the full precision that the plain timestamp and timestamptz data types support.

select ('2021-05-16T22:58:53.122521+00:00'::timestamptz) at time zone 'UTC' as "absolute time";

(The T that separates the date and the time fields in the web service's format is accepted in a plain timestamp or timestamptz literal. But it is not generated by the ::text type cast of a timestamp or timestamptz value.)

PostgreSQL, and therefore YSQL, represent absolute time using the familiar Western calendar. You can think of a calendar as a look-up-table for converting between the number of seconds since the start of some epoch and a date-and-time.

The internal representation is of no significance for the semantics of what is represented. But you might like to visualize the representation as an integer that represents the number of microseconds from the start of the epoch through to the moment of interest. Try this:

select
  (  '4713-01-01 00:00:00.00000 BC'::timestamp)::text as min_ts,
  ('294276-01-01 00:00:00.00000 AD'::timestamp)::text as max_ts;

It quietly succeeds and produces the obvious result. (The ::text typecast of a timestamp value uses only as much precision as the value requires.)

         min_ts         |        max_ts
------------------------+-----------------------
 4713-01-01 00:00:00 BC | 294276-01-01 00:00:00

The values, 4713 BC and 294276 AD, are given in the table in the "Introduction" section to the present date-time data types major section.

Wall-clock-time and local time

The history of human culture has established the tradition that no matter at what longitude people live, they want to organize their day around the coming and going of daylight (even in places that at some times of the year never experience daylight). "Noon" is a universal notion, and all languages have a word for it—just as they do for "mother" and "father". Wall-clock-time, according to the purist definition, is what you read off a sundial, wherever you happen to be.

People came to understand a long time ago, when reliable mechanical clocks were first invented, that the absolute time at which noon occurs as you circumnavigate along any line of latitude (i.e. as you pass through all longitude values) is not a constant. Rather, it varies continuously.

A local time represents a conventionalized wall-clock-time. You cannot map between these two kinds of values unless you know in which timezone (see below) the "sundial" is located. Moreover, the conversion is non-trivial. All sorts of social factors, like conventions about Daylight Savings Time, matter. But there is no need to rehearse that story here. Modern IT systems, including SQL database systems, have encoded all of the relevant rules. Local time, in a particular timezone quantizes sundial time so that it typically changes in one hour steps when you cross the boundary between adjacent timezones. Some timezones, however, specify offsets from the UTC Time Standard with half-hour, or even quarter-hour, granularity. For example, Asia/Kabul specifies an offset of 04:30; and Asia/Kathmandu specifies an offset of 05:45. (Neither Asia/Kabul nor Asia/Kathmandu observes Daylight Savings Time.)

Timezones and the offset from the UTC Time Standard

People around the world prefer a convention that says that noon occurs at 12:00 local time. You need to drive only about 100 km to the west (depending on your latitude, to see that sundial noon occurs five minutes later at your journey's destination than at its start. Clearly, not everybody can be happy. This leads to the timezone notion. Loosely, a clock set to local time (like the one on your smartphone) will show something between 11:30 and 12:30 at sundial noon wherever you are on the planet according to the timezone you're in—"loosely", of course, because of two things: firstly, because some timezones, like, Asia/Shanghai (this is the canonical name for the timezone that covers the whole of the PRC) cover a much bigger latitude span than corresponds to just a one hour change in the absolute time at which noon occurs); and secondly, because of Daylight Savings Time.

• Look at the table shown in Real timezones that observe Daylight Savings Time. (Real means that these canonically-named timezones, unlike UTC, are associated with specified geographic regions.) It lists 114 timezones.

• Now look at the table shown in Real timezones that don't observe Daylight Savings Time. It lists 68 timezones.

A real timezone, according to a purist definition of this term of art, denotes a region, or maybe a set of regions, where, by an agreed convention, all correctly set wall-clocks show the same date and time. These days, the notion of a correctly set wall-clock is immediately understood as what a smartphone shows when the option is selected to set the date and the time automatically. Your smartphone knows where it is and, from this, it knows what timezone it's in. It has access to the UTC time (the absolute date-and-time). And it has access to various facts that characterize its present timezone—in particular, the current offset in hours and minutes from UTC. (The qualifier current is important because, in general, the offset from UTC depends on the date according to whether or not the timezone observes Daylight Savings Time.)

See the Wikipedia article List of tz database time zones. It has close to six hundred entries, uniquely identified by name. The source of the information is the tz database:

The tz database is a collaborative compilation of information about the world's time zones, primarily intended for use with computer programs and operating systems... [It has] the organizational backing of ICANN. The tz database is also known as tzdata, the zoneinfo database or IANA time zone database...

PostgreSQL, and therefore YSQL, expose a projection of the columns shown in the List of tz database time zones as the pg_timezone_names catalog view.

The tz_database reveals a mish-mash of heterogenous naming conventions. This stems from the history of conventional thinking in this space. Notice that entries have a status label which includes "canonical". A canonical entry is identified by a primary, preferred zone name. Here are some examples of canonically named timezones: Europe/London, America/Los_Angeles, Africa/Johannesburg, and Asia/Shanghai.

Many differently-named timezones can all denote the same offset from UTC on a particular date. Moreover, the regions that differently named timezones denote might well overlap. This is the critically important fact:

• A timezone name uniquely determines the offset from the UTC Time Standard (on any date of interest). The offset might be different at different times of year. Moreover, over the course of centuries, the size of the offset on a particular day and month need not be unique because of changes in convention.

Notice that the tz_database has an entry with the name UTC. Strictly speaking, this is not a timezone. Rather, it is a time standard. See the article The Difference Between GMT and UTC:

UTC is not a time zone, but a time standard that is the basis for civil time and time zones worldwide. This means that no country or territory officially uses UTC as a local time.

UTC is not tied to a region.

Try this:

select name, utc_offset::text
from pg_timezone_names
where
  name = 'UTC' or
  (name like 'Etc/GMT%' and utc_offset <> make_interval())
order by utc_offset;

The reason for the slightly elaborate restriction is that there are rows with names 'Etc/GMT-0', 'Etc/GMT', and 'Etc/GMT0', all of which have an offset of zero from UTC. These entries are therefore redundant.

This is the result:

 Etc/GMT+12 | -12:00:00
 Etc/GMT+11 | -11:00:00
 Etc/GMT+10 | -10:00:00
 Etc/GMT+9  | -09:00:00
 Etc/GMT+8  | -08:00:00
 Etc/GMT+7  | -07:00:00
 Etc/GMT+6  | -06:00:00
 Etc/GMT+5  | -05:00:00
 Etc/GMT+4  | -04:00:00
 Etc/GMT+3  | -03:00:00
 Etc/GMT+2  | -02:00:00
 Etc/GMT+1  | -01:00:00
 UTC        |  00:00:00
 Etc/GMT-1  |  01:00:00
 Etc/GMT-2  |  02:00:00
 Etc/GMT-3  |  03:00:00
 Etc/GMT-4  |  04:00:00
 Etc/GMT-5  |  05:00:00
 Etc/GMT-6  |  06:00:00
 Etc/GMT-7  |  07:00:00
 Etc/GMT-8  |  08:00:00
 Etc/GMT-9  |  09:00:00
 Etc/GMT-10 |  10:00:00
 Etc/GMT-11 |  11:00:00
 Etc/GMT-12 |  12:00:00
 Etc/GMT-13 |  13:00:00
 Etc/GMT-14 |  14:00:00

Look these up in the List of tz database time zones. They do not have a country code, latitude and longitude, or specification of the region covered. In this sense, they are like UTC. They are simply canonical names for time standards with particular offsets from UTC. All of the offsets are an integral number of hours. Such timezones will be referred to as pseudotimezones in the overall Date and time data types section.

The section The extended_timezone_names view explains how to define this view. It joins the native catalog view with extra facts from the List of tz database time zones.

The strange history of timezones

Not only does a timezone label encode the start and end dates for Daylight Savings Time (when this is observed); it also encodes, for example, when radical calendar adjustments were made. Look at the Wikipedia article Standard Time in the United States. It starts with this:

The history of Standard Time in the United States began November 18, 1883, when United States and Canadian railroads instituted Standard Time in timezones. Before then, time of day was a local matter, and most cities and towns used some form of local solar time, maintained by some well-known clock (for example, on a church steeple or in a jeweler's window). The new Standard Time system was not immediately embraced by all.

Create and execute the function historical_timezone_changes():

drop function if exists historical_timezone_changes() cascade;

create function historical_timezone_changes()
  returns table(x text)
  language plpgsql
as $body$
declare
  zones constant text[] not null := array[
    'Europe/London',
    'Europe/Bucharest',
    'Europe/Helsinki',
    'Europe/Oslo',
    'Europe/Paris',
    'America/New_York',
    'America/Chicago',
    'America/Denver',
    'America/Los_Angeles',
    'Pacific/Honolulu'];
  z text not null := zones[1];

  r record;
  stmt constant text := $$set timezone = '%s'$$;
begin
  foreach z in array zones loop
    execute format(stmt, z);

    select
      current_setting('timezone')                                                        as f1,
      to_char('1883-11-18 16:00:00 +00:00'::timestamptz, 'TZH:TZM hh24:mi:ss Dy dd-Mon') as f2,
      to_char('1883-11-18 20:00:00 +00:00'::timestamptz, 'TZH:TZM hh24:mi:ss Dy dd-Mon') as f3,
      to_char('2000-11-18 20:00:00 +00:00'::timestamptz, 'TZH:TZM hh24:mi:ss Dy dd-Mon') as f4
    into r;

    x := rpad(r.f1, 21)||rpad(r.f2, 33)||rpad(r.f3, 33)||r.f4;
    return next;
  end loop;
end;
$body$;

select x from historical_timezone_changes();

This is the result. (The blank lines were added manually to improve the readability.)

 Europe/London        +00:00 16:00:00 Sun 18-Nov       +00:00 20:00:00 Sun 18-Nov       +00:00 20:00:00 Sat 18-Nov

 Europe/Bucharest     +01:44 17:44:24 Sun 18-Nov       +01:44 21:44:24 Sun 18-Nov       +02:00 22:00:00 Sat 18-Nov
 Europe/Helsinki      +01:39 17:39:49 Sun 18-Nov       +01:39 21:39:49 Sun 18-Nov       +02:00 22:00:00 Sat 18-Nov
 Europe/Oslo          +00:43 16:43:00 Sun 18-Nov       +00:43 20:43:00 Sun 18-Nov       +01:00 21:00:00 Sat 18-Nov
 Europe/Paris         +00:09 16:09:21 Sun 18-Nov       +00:09 20:09:21 Sun 18-Nov       +01:00 21:00:00 Sat 18-Nov

 America/New_York     -04:56 11:03:58 Sun 18-Nov       -05:00 15:00:00 Sun 18-Nov       -05:00 15:00:00 Sat 18-Nov
 America/Chicago      -05:50 10:09:24 Sun 18-Nov       -06:00 14:00:00 Sun 18-Nov       -06:00 14:00:00 Sat 18-Nov
 America/Denver       -06:59 09:00:04 Sun 18-Nov       -07:00 13:00:00 Sun 18-Nov       -07:00 13:00:00 Sat 18-Nov
 America/Los_Angeles  -07:52 08:07:02 Sun 18-Nov       -08:00 12:00:00 Sun 18-Nov       -08:00 12:00:00 Sat 18-Nov3

 Pacific/Honolulu     -10:31 05:28:34 Sun 18-Nov       -10:31 09:28:34 Sun 18-Nov       -10:00 10:00:00 Sat 18-Nov

You can see that, just as the Wikipedia article said, a conventional adjustment was edicted for clocks in the United States during the period from 16:00 UTC through 20:00 UTC on Sunday 18-Nov-1883. During that period (in fact, successively from region to region) everybody "gained" a little time—just as, now, people "gain" one hour on the "fall back" day when Daylight Savings Time ends. For example, people in the America/Los_Angeles zone gained seven minutes and two seconds. Moreover, the offset between these regions and Greenwich UK was adjusted by a small compensating amount at the same transition moment to make it an integral multiple of one hour.

Notice that the time in London was always right—by definition! Meanwhile, other cities in Europe made their corresponding conventional adjustments at later dates.

By the year 2000, all the cities or regions that the query selects, whose timezones the function uses, have settled down to have timezone offsets that are integral multiples of one hour. Notably, some timezones have offsets that are integral multiples of fifteen minutes. Try this:

select name, utc_offset::text
from pg_timezone_names
where extract(minute from utc_offset) <> 0
order by utc_offset;

You get results like these:

 Pacific/Marquesas     |    -09:30
 America/St_Johns      |    -02:30
 Asia/Tehran           |     04:30
 Asia/Kabul            |     04:30
 Asia/Colombo          |     05:30
 Asia/Kolkata          |     05:30
 Asia/Kathmandu        |     05:45
 Indian/Cocos          |     06:30
 Asia/Yangon           |     06:30
 Australia/Eucla       |     08:45
 Australia/Darwin      |     09:30
 Australia/Adelaide    |     09:30
 Australia/Broken_Hill |     09:30
 Australia/Lord_Howe   |     10:30
 Pacific/Chatham       |     12:45

This output was massaged by hand for readability: it retains only those zones that, in the List of tz database time zones, have the status Canonical; and the seconds field of the display of the offset has been elided because it's always zero. The section The extended_pg_timezone_names view explains how to create a view and write a query that produces the output as shown without massage.

Two ways of conceiving of time: calendar-time and clock-time

For obvious reasons, most known ancient human cultures, from time immemorial, seem to have measured time in terms of conspicuous observable natural cycles: the daily cycle, the monthly cycle, and the yearly cycle. The manifestation of the daily cycle is pressingly obvious. The monthly cycle is characterized by the phases of the moon. The yearly cycle is characterized (in most parts of the world) by the seasons.

As the ability to count developed, a (lunar) month was seen to be a roughly constant number of days, and a year was seen to be a roughly constant number of (lunar) months.

It was only relatively recently in the history of human culture that clocks were invented: first sundials, then mechanical clocks, and eventually atomic clocks and in particular the caesium standard. The adoption of hours, minutes, and seconds (as successive subdivisions of the length of a day) came hand in hand with the invention of clocks. An increase in the accuracy and precision of clocks, and an improvement in observation and thinking in astronomy, brought the realization that one year is not exactly equal to 365 days—as nor need it be. There's no reason to expect that the time it takes for the earth to orbit the sun (one year) should be an integral multiple of the time between successive noons (one day).

It was eventually decided that an atomic clock should define the ultimate standard unit of time and that the second should be defined as a multiple of the caesium standard unit. One second is defined to be 9,192,631,770 caesium units. See the Wikipedia article Second. One minute is defined as exactly 60 seconds. And one hour is defined as exactly 60 minutes. You might be tempted to extend this by saying that one day is exactly 24 hours. But this is complicated by the business of Daylight Savings Time. See Calendar-time below.

It is now understood that the observable phenomena like day, month, and year cannot be defined as constant integral numbers of seconds.

This realization is formalized in PostgreSQL (and therefore in YSQL) as the so-called proleptic Gregorian calendar.

This mapping is exposed in the seconds-to-calendar direction by the built-in function to_timestamp(). The return data type is timestamptz so that the result can be correctly expressed for any chosen timezone. And the mapping is implemented in the calendar-to-seconds direction by extract epoch.

Human history, and the calendar that it brings, explain why there are two ways of conceiving of the time at some moment and of the duration between two moments of interest.

Calendar-time

Years, months, and days are calendar units. Days must be taken, in the general case, with calendar-time-semantics because Daylight Savings Time is a matter of convention (i.e. a cultural phenomenon rather than an aspect of the laws of physics). Calendar-time brings the consequence that while a day is normally 24 hours, it will be 23 hours around the "spring forward" moment and 25 hours around the "fall back" moment in a timezone that adopts Daylight Savings Time.

Using calendar-time-semantics:

• One year later than some moment is the same date in the next year (and the same time on that date). Special rules are needed when you start on 29-February in a leap year.

• One month later than a moment on the Nth day of a particular month is always the Nth day of the next month (and the same time on that date), regardless of whether the actual duration might be 28, 29, 30, or 31 days. Again, special rules are needed when the starting day is the 29th of some month, or later, and when the target month has no such date.

• One day later than a moment the day before the "spring forward" or "fall back" date in a regime where Daylight Savings Time is observed is simply the same time of day on the next date.

The fact that the clock-time-semantics duration of identically specified calendar-time-semantics durations is not constant is defined to be unimportant. You cannot convert deterministically, in either direction, between a calendar-time-semantics duration (years, months, and days) and a clock-time-semantics duration (hours, minutes and seconds) except by asserting an arbitrary rule of thumb.

The calendar encodes all of the relevant special rules—with the critical exception of the facts about Daylights Savings Time periods in all timezones for all years that the calendar spans. (This information is encoded separately as a mapping from timezone to transition dates.)

Treating one day as exactly 24 hours, one month as 30 days, and one year as 12 months (or alternatively as 365 days) brings inaccuracies and mutual contradictions. Twelve months (30*60 days) is just 360 days—and not 365 (or 366) days. None of this matters at all when calendar-time-semantics durations are needed, or given, only approximately. When you ask a child how old she is, and she answers "seven and three-quarters", this denotes a necessarily approximate calendar-time-semantics duration. And the precision implied by how the duration is stated ("seven and three-quarters" rather then "eight") swamps any concerns about the exact durations of the days, months, and years that the child's lifetime spans.

Clock-time

Briefly, clock-time is caesium units—or, by convention, seconds, minutes, and hours.

Note: Some calendar implementations support leap seconds. (When a leap second occurs, its containing minute is 61 seconds and not 60 seconds.) But the implementation that PostgreSQL and YSQL use does not support this notion. The implementation, therefore, can treat hours, minutes, and seconds with strict clock-time-semantics and needs to treat only the larger units with calendar-time-semantics.

When you ask "How long did the Apollo 11 mission last from launch to splashdown in the Pacific Ocean?" you prefer the answer to use clock-time-semantics:

exactly twenty-seven hours, 18 minutes and 35 seconds.

In the same spirit, the time it will take to get to Mars is best expressed (according to current estimates) as:

about 4,800 hours.

Of course, you might prefer to use larger units so that you can quote smaller numbers. But you must accept that doing so implies that the numbers are approximate. It's reasonable to say that it will take about six and a half months to get to Mars because "about" preempts any concern about the fact that different months have different lengths.

Practical examples

The section Interval arithmetic, within the enclosing section The interval data type and its variants, explains the semantics of computing an interval value by subtracting one timestamp(tz) value from another or adding an interval value to, for example, a timestamp(tz) value. Briefly, calendar-time_semantics is always used for the years, months, and days fields. And clock-time-semantics is always used for the hours, minutes, and seconds fields. Clock-time-semantics is used to compute the days field when one timestamp(tz) value is subtracted from another (using the rule of thumb that one day is always exactly twenty-four hours). And calendar-time-semantics is used when a days field is added to a timestamp(tz) value. This asymmetry might surprise you.

with v as (
  select (
      -- The 'America/Los_Angeles' timezone adopted Standard Time at
      -- some moment between these two timestamptz values.
      extract(epoch from '1883-11-19 08:00:00 America/Los_Angeles'::timestamptz) -
      extract(epoch from '1883-11-18 08:00:00 America/Los_Angeles'::timestamptz)
    ) as delta
  )
select
  make_interval(secs=>delta)::text as "delta (hh:mi:ss)"
from v;

 delta (hh:mi:ss)
------------------
 24:07:02

set timezone = 'America/Los_Angeles';
select
  '1883-11-18 08:00:00 America/Los_Angeles'::timestamptz + '24 hours' ::interval as "t0 + 24 hours",
  '1883-11-18 08:00:00 America/Los_Angeles'::timestamptz + '1 day'    ::interval as "t0 + 1 day";

     t0 + 24 hours      |       t0 + 1 day
------------------------+------------------------
 1883-11-19 07:52:58-08 | 1883-11-19 08:00:00-08