The plain timestamp and timestamptz data types

Understanding this section depends on understanding the 'Timezones' section.

To understand the timestamptz data type, and converting its values to/from plain timestamp values, you need to understand what the Timezones and UTC offsets section explains.

The plain timestamp data type and the timestamptz data type are cousins. But there are critical differences:

  • Both a plain timestamp datum and a timestamptz datum have the identical internal representation. You can picture it as the real number of seconds (with microsecond precision) from a reference moment (12:00 on 1-Jan-1970, UTC). The extract(epoch from t) function, where t is either a plain timestamp value or a timestamptz value, returns this number. Moreover, the result is independent of the session's current TimeZone setting for both of these data types. (See the subsection Interpretation and statement of the rules below.)
  • The difference is in the metadata that describes the datum: each knows which kind it is. And the difference is significant when a datum is recorded or read back.

You need a clear understanding of the differences so that you can make the appropriate choice between these two data types according to the use case.

You need a very good reason to prefer plain 'timestamp' to 'timestamptz'.

You should definitely consider timestamptz to be your default choice for a persistent representation. And you should be able to write down a clear account of your reasoning when you decide that plain timestamp is the better choice in the present application design context.

For example, you might choose plain timestamp to send the representation of a moment of interest to a client (or to receive it back from a client) where the scenario in which the value is used has pre-defined the reigning timezone as a defining property for the ongoing scenario. Here, you'd convert from/to the ultimate timestamptz value to/from plain timestamp at the timezone of interest and then you'd convert this to/from text, using the specified representation format, to send to, or receive from, the client.

The plain timestamp data type

Plain timestamp values represent the date and the time-of-day of some moment in a local time regime. There is no timezone-sensitivity, neither when a plain timestamp value is created, nor when it is read. Such a value therefore represents a moment at some unspecified location—just as a clockwork wall-clock that shows the date does. You can picture a timestamp value as the number of microseconds to the present moment from the start of some epoch.

Because there's no timezone-sensitivity, there's no sensitivity to Daylight Savings regimes either: every day runs from midnight (inclusive) through the next midnight (exclusive). (PostgresSQL, and therefore YSQL, don't support leap seconds.) However the same quirk that allows '24:00:00' as a time value allows this as the time-of-day component when you specify a timestamp value. Try this:

select
  ('2021-02-14 00:00:00.000000'::timestamp)::text as "starting midnight",
  ('2021-02-14 23:59:59.999999'::timestamp)::text as "just before ending midnight",
  ('2021-02-14 24:00:00.000000'::timestamp)::text as "ending midnight";

This is the result:

  starting midnight  | just before ending midnight |   ending midnight
---------------------+-----------------------------+---------------------
 2021-02-14 00:00:00 | 2021-02-14 23:59:59.999999  | 2021-02-15 00:00:00

The fact that this is allowed is less of an annoyance than it is with time values because, now that the date is part of the picture, the obvious convention can be used to render a time-of-day component given as '24:00:00' on one day as '00:00:00' on the next day. This convention has a knock on effect on the result of subtracting one timestamp value from another timestamp value. Try this:

select
  ('2021-02-14 24:00:00'::timestamp - '2021-02-14 00:00:00'::timestamp)::text as "the interval";

This is the result:

 the interval
--------------
 1 day

The timestamptz data type

A timestamptz value represents an absolute time. Such a value can therefore be deterministically expressed as the local time in the UTC timezone—or, more carefully stated, as the local time at an offset of '0 seconds'::interval with respect to the UTC Time Standard.

Though the representations of an actual plain timestamp value and an actual timestamptz value are the same, the semantics, and therefore the interpretation, is different for a timestamptz value than for a plain timestamp value because of the difference in the metadata of the values.

  • When a timestamptz value is assigned, the UTC offset must also be specified. Once a timestamptz value has been recorded (for example, in a table column with that data type), the representation doesn't remember what the offset was at the moment of recording. That information is entirely consumed when the incoming value is normalized to UTC.
  • When a timestamptz value is converted to a text value for display (either implicitly using the ::text typecast or explicitly using the to_char() built-in function), the conversion normalizes the date-time component so that it shows the local time with respect to the UTC offset that the current timezone specifies.

The UTC offset may be specified implicitly (using the session's current TimeZone setting) or explicitly—either within the text of the timestamptz literal, or using the at time zone operator. Further, the specification of the offset may be explicit, as an interval value, or implicit using the name of a timezone. When a timezone observes Daylight Savings Time, it's name denotes different offsets during the Standard Time period and the Summer Time period.

The rules for this, and examples that show all of the possible ways to assign a timestamptz value, are given in the section Timezones and UTC offsets and its subsections.

A small illustration

Create a test table with a timestamptz column, insert one row, and view the result using, successively, two different values for the session's current timezone setting.

drop table if exists t cascade;
create table t(k int primary key, v timestamptz not null);

-- This setting has no effect on the inserted value.
set timezone = 'Europe/Paris';
insert into t(k, v) values(1, '2021-02-14 13:30:35+03:00'::timestamptz);

set timezone = 'America/Los_Angeles';
select v::text as "timestamptz value" from t where k = 1;

set timezone = 'Asia/Shanghai';
select v::text as "timestamptz value" from t where k = 1;

This is the result of the first query:

 2021-02-14 02:30:35-08

And this is the result of the second query:

 2021-02-14 18:30:35+08

This outcome needs careful interpretation. It turns out that, using ordinary SQL, there is no direct way to inspect what is actually held by the internal representation as an easily-readable date-time value.

  • You can of course, apply the at time zone 'UTC' operator to the value of interest. But this implies understanding what you see in the light of a rule that you must accept. And this section aims to demonstrate that the rule in question is correct, given that you know already what value is internally represented—in other words, you'd be "proving" that you understand correctly by assuming that you have!
  • The better way is to use the extract(epoch from timestamptz_value) function. But even this requires an act of faith (or lots of empirical testing): you must be convinced that the result of extract() here is not sensitive to the current TimeZone setting. The demonstration shows that you can indeed rely on this.

Usually, you "see" the value represented only indirectly. In the present case—for example as the ::text typecast of the value. And the evaluation of this typecast is sensitive to the current TimeZone setting.

You can readily understand that the three values '2021-02-14 13:30:35+03:00', ' 2021-02-14 02:30:35-08', and '2021-02-14 18:30:35+08' all represent the very same actual absolute time. This is like the way that (10 + 1) and (14 - 3) both represent the same value. However, the apparent redundancy brought by the possibility of many different ::text typecasts of the same underlying value is useful for human readability in a scenario like this:

We'll talk next Tuesday at 08:00 my time (i.e. UTC-8)—in other words 17:00 your time (i.e. UTC+1).

The meeting partners both have a background knowledge of their timezone. But the important fact for each, for the day of the meeting, is what time to set the reminder on their clock (which setting is done only in terms of the local time of day): respectively 08:00 and 17:00.

Notice that when a timezone respects Daylight Savings Time, this is taken account of just like it is in the example above.

A minimal simulation of a calendar application

Consider this scenario:

  • Rickie, who lives in Los Angeles, has constraints set by her family—and she controls the meeting. She can manage only eight o'clock in the morning. It's unimportant to her whether Daylight Savings Time is in force or not because her local constraining events (like when school starts) are all fixed in local time—and only eight in the morning local time works for her. She needs to fix two Tuesday meetings that happen to straddle the "spring forward" moment in Los Angeles—and then to see each listed as at eight o'clock in her online calendar.
  • Vincent, who lives in Amsterdam, needs to see when these meetings will take place in his online calendar.

Simulate a very specialized calendar application that deals with just this single scenario. (Never mind that it's unrealistic. It makes the desired teaching point.)

-- This is done when the app is installed.
drop table if exists meetings cascade;
create table meetings(k int primary key, t timestamptz);

deallocate all;

prepare cr_mtg(int, text, int, text) as
insert into meetings (k, t) values
  ($1, $2::timestamptz),
  ($3, $4::timestamptz);

prepare qry_mtg as
select
  k                                                                  as "Mtg",
  to_char(t, 'Dy hh24-mi on dd-Mon-yyyy TZ ["with offset" TZH:TZM]') as "When"
from meetings
order by k;

Now simulate Rickie creating the meetings. She has the timezone America/Los_Angeles set in her calendar preferences:

set timezone = 'America/Los_Angeles';
execute cr_mtg(
  1, '2021-03-09 08:00:00',
  2, '2021-03-16 08:00:00');
execute qry_mtg;

This is Rickie's result:

 Mtg |                       When
-----+---------------------------------------------------
   1 | Tue 08-00 on 09-Mar-2021 PST [with offset -08:00]
   2 | Tue 08-00 on 16-Mar-2021 PDT [with offset -07:00]

Rickie gets a gentle reminder that the meetings do happen to straddle her "spring forward" moment. But she doesn't really care. She confirms that each meeting will happen at eight.

Now simulate Vincent attending the meetings. He has the timezone Europe/Amsterdam set in his calendar preferences:

set timezone = 'Europe/Amsterdam';
execute qry_mtg;

This is Vincent's result:

 Mtg |                       When
-----+---------------------------------------------------
   1 | Tue 17-00 on 09-Mar-2021 CET [with offset +01:00]
   2 | Tue 16-00 on 16-Mar-2021 CET [with offset +01:00]

Because Europe's "spring forward" moment is two weeks after it is in the US, Vincent sees that the second meeting is one hour earlier than the first while the timezone specification is unchanged. If he doesn't know that the US is out of step on Daylight Savings Time, he might think that Rickie has simply done this on a whim. But this hardly matters: he knows when he has to attend each meeting.

Imagine trying to write the logic that brings the correct, and useful, functionality that the code above demonstrates if you used the bare timestamp data type. The code would be voluminous, obscure, and very likely to be buggy. In contrast, the timestamptz data type brought the required functionality with no application code except to set the timezone specifically for each user.

Another way to "see" a timestamptz value is to compare it with what you reason it will be. Try this:

drop table if exists t cascade;
create table t(k int primary key, v timestamptz not null);

set timezone = 'Europe/Helsinki';
-- The timezone of the inserted value is set implicitly.
insert into t(k, v) values(1, '2021-02-14 13:30:35'::timestamptz);
select (
    (select v from t where k = 1) = '2021-02-14 11:30:35 UTC'::timestamptz
  )::text;

The result is true.

More Daylight Savings Time examples

The US recognizes Daylight Savings Time. It starts, in 2021, in the 'America/Los_Angeles' zone, on 14-Mar at 02:00. Watch what a clock that automatically adjusts according to Daylight Savings Time start/end moments (like on a smartphone) does. It goes from '01:59:59' to '03:00:00'. Try this:

set timezone = 'America/Los_Angeles';
select
  '2021-03-14 01:30:00 America/Los_Angeles'::timestamptz as "before",
  '2021-03-14 02:30:00 America/Los_Angeles'::timestamptz as "wierd",
  '2021-03-14 03:30:00 America/Los_Angeles'::timestamptz as "after";

This is the result:

         before         |         wierd          |         after
------------------------+------------------------+------------------------
 2021-03-14 01:30:00-08 | 2021-03-14 03:30:00-07 | 2021-03-14 03:30:00-07

The value in the column with the alias "weird" is weird because '2021-03-14 02:30:00' doesn't exist. The design could have made the attempt to set this cause an error. But it was decided that it be forgiving.

Daylight Savings Time in the America/Los_Angeles timezone ends, in 2021, on 7-Nov at 02:00:00. Watch what a clock that automatically adjusts according to Daylight Savings Time start/end does now. It falls back from '01:59:59' to '01:00:00'. This means that, for example, '01:30:00' on 7-Nov is ambiguous. If you ring your room-mate latish on Saturday evening 6-Nov to say that you'll won't be back home until the small hours, probably about one-thirty, they won't know what you mean because the clock will read this time twice. It's easiest to see this in reverse. Try this:

set timezone = 'America/Los_Angeles';
select
  to_char('2021-11-07 08:30:00 UTC'::timestamptz, 'hh24:mi:ss TZ (OF)') as "1st 1:30",
  to_char('2021-11-07 09:30:00 UTC'::timestamptz, 'hh24:mi:ss TZ (OF)') as "2nd 1:30";

This is the result:

      1st 1:30      |      2nd 1:30
--------------------+--------------------
 01:30:00 PDT (-07) | 01:30:00 PST (-08)

So you have to tell them that you'll be back in an elaborate way:

  • either at about one-thirty PDT (or about one-thirty PST)
  • or equivalently at about one-thirty before the fall back moment (or one-thirty after the fall back moment)
  • or at about one-thirty UTC-7 (or about one-thirty UTC-8)
  • or even at about eight-thirty UTC (or nine-thirty UTC).

Confused? Your room-mate soon will be!

This strange (but logically unavoidable) consequence of observing Daylight Savings Time means that you have to be careful in the other direction too. Try this:

select
  to_char(('2021-11-07 01:30:00 America/Los_Angeles'::timestamptz) at time zone 'UTC', 'hh24:mi:ss') as "Ambiguous";

This is the result:

 Ambiguous
-----------
 09:30:00

PostgreSQL (and therefore YSQL) resolve the ambiguity by convention: the later moment is chosen. Express what you mean explicitly to avoid the ambiguity:

set timezone = 'UTC';
select
  to_char('2021-11-07 01:30:00 -07:00'::timestamptz, 'hh24:mi:ss TZ') as "Before fallback",
  to_char('2021-11-07 01:30:00 -08:00'::timestamptz, 'hh24:mi:ss TZ') as "After fallback";

This is the result:

 Before fallback | After fallback
-----------------+----------------
 08:30:00 UTC    | 09:30:00 UTC

Or, according to the display that best suits your purpose, you might do this instead:

set timezone = 'America/Los_Angeles';
select
  to_char('2021-11-07 01:30:00 -07:00'::timestamptz, 'hh24:mi:ss TZ') as "Before fallback",
  to_char('2021-11-07 01:30:00 -08:00'::timestamptz, 'hh24:mi:ss TZ') as "After fallback";

This is the now result:

 Before fallback | After fallback
-----------------+----------------
 01:30:00 PDT    | 01:30:00 PST

The mapping 'abbrev' to 'utc_offset' in the 'pg_timezone_names' view isn't unique.

Try this:

select name, abbrev, lpad(utc_offset::text, 9) as "UTC offset"
from pg_timezone_names
where abbrev in ('PST', 'PDT')
order by utc_offset;

This is the result:

         name         | abbrev | UTC offset
----------------------+--------+------------
 Canada/Yukon         | PDT    | -07:00:00
 ...
 America/Los_Angeles  | PDT    | -07:00:00
 ...
 Canada/Pacific       | PDT    | -07:00:00


 Asia/Manila          | PST    |  08:00:00

Some results were elided. The blank lines were added manually to improve the readability.

However, the [name, abbrev] tuple does uniquely identify a utc_offset value.

You might be tempted to write PDT and PST in the example above in place of -07:00 and -08:00. That would work, in this specific use, but it won't work reliably in general because the abbreviations are looked up internally in pg_timezone_abbrevs.abbrev. But such abbreviations are never looked up in pg_timezone_names.abbrev. However, some abbreviations are found only in pg_timezone_names.abbrev. This can confuse the application programmer and lead, in some unfortunate circumstances, even to wrong results. The complex rules in this space are explained in the section Rules for resolving a string that's intended to identify a UTC offset.

Yugabyte recommends that you program defensively to avoid these pitfalls and follow the approach described in the section Recommended practice for specifying the UTC offset.

Demonstrating the rule for displaying a timestamptz value in a timezone-insensitive way

The code blocks above, and especially those in the section More Daylight Savings Time examples, are just that: examples that show the functional benefit that the timestamptz data type brings. The outcomes that are shown accord with intuition. But, so that you can write reliable application code, you must also understand the rules that explain, and let you reliably predict, these beneficial outcomes.

The philosophy of the demonstration's design

The demonstration uses the timestamptz_vs_plain_timestamp() table function. The overall shape of this is very similar to that of the table function plain_timestamp_to_from_timestamp_tz() presented in the "sensitivity of the conversion between timestamptz and plain timestamp to the UTC offset" section.

The demonstration does two things:

  • It shows that the result produced by the extract(epoch from timestamp-[tz]-value) function is insensitive to the session's Time_Zone setting—for both data types.

  • It tests the correctness of tersest statement the underlying semantic rules for the text to timestamptz conversion.

The demonstration that follows is designed like this:

  • A table function, timestamptz_vs_plain_timestamp(), is used to enable a convenient "running commentary" visualization.

  • Two constant values, one with data type plain timestamp and one with data type timestamptz are initialized so that the internal representations (as opposed to the metadata) are identical. Look:

    ts_plain    constant timestamp   not null := make_timestamp  (yyyy, mm, dd, hh, mi, ss);
ts_with_tz  constant timestamptz not null := make_timestamptz(yyyy, mm, dd, hh, mi, ss, 'UTC');

  • Each uses the same constant int values, yyyy, mm, dd, hh, mi, and ss, to define the identical date-and-time part for each of the two moments. The fact that UTC is used for the timezone argument of the make_timestamptz() invocation ensures the required identity of the internal representations of the two moments—actually, both as plain timestamp values.

  • The extract(epoch from ... ) function is used to get the numbers of seconds, as constant double precision values, from the start of the epoch for the two moment values. An assert statement confirms that these two numbers of seconds are identical.

  • A constant array, timezones, is populated by this query:

    select name
from pg_timezone_names
where name like 'Etc/GMT%'
and utc_offset <> make_interval()
order by utc_offset;

  • The query produces timezones that are listed on the synthetic timezones page. This is a convenient way to define a set of UTC offset values, independently of when during the winter or summer you execute the query, that span the range from -12:00 to +14:00 in steps of one hour.

  • A foreach loop is run thus:

    foreach z in array timezones loop

  • At each loop iteration:

    • The session's TimeZone setting is set to the value that the iterand, z, specifies.

    • The extract(epoch from ... ) function is used again on the reference plain timestamp and timestamptz values (ts_plain, and ts_with_tz) at the present TimeZone setting. An assert statement shows that these newly-extracted values are always identical to the initially extracted values—in other words that they are unaffected by that setting. Notice that the initially extracted values were obtained at whatever the session's TimeZone setting happens to be on entry to the function.

    • Using the utc_offset() user-defined function (it looks up the UTC offset for the timezone z in the pg_timezone_names catalog view) these values are obtained:

      t1             double precision := extract(epoch from ts_plain);
t2             double precision := extract(epoch from ts_with_tz);
tz_of_timezone interval         := utc_offset(z);
tz_display     interval         := to_char(ts_with_tz, 'TZH:TZM');
ts_display     timestamp        := to_char(ts_with_tz, 'yyyy-mm-dd hh24:mi:ss');
delta          interval         := ts_display - ts_plain;

    • These assert statements are executed:

      assert (t1 = ts_plain_epoch),         'Assert #1 failed';
assert (t2 = ts_with_tz_epoch),       'Assert #2 failed';
assert (tz_display = tz_of_timezone), 'Assert #3 failed';
assert (tz_display = delta),          'Assert #4 failed';

    • Running commentary output is generated thus:

      report_line(z, ts_plain, ts_with_tz);

  • Finally, after the loop completes and before exiting, the session's TimeZone setting is restored to the value that it had on entry to the function. (It's always good practice to do this for any settings that your programs need, temporarily, to change.)

The demonstration

The timestamptz_vs_plain_timestamp() table function uses two helper functions. The function utc_offset() gets the UTC offset, as an interval value, for the specified timezone thus:

drop function if exists utc_offset(text) cascade;

create function utc_offset(tz_name in text)
  returns interval
  language plpgsql
as $body$
declare
  i constant interval not null := (
                                    select a.utc_offset from
                                    pg_timezone_names a
                                    where a.name = tz_name
                                  );
begin
  return i;
end;
$body$;

The function report_line() formats the name of the session's current TimeZone setting and the reference constant timestamp and constant timestamptz values (ts_plain and ts_with_tz) for maximally easily readable output.

Critical comment: It's this formatting action that demonstrates the sensitivity of the display of a timestamptz value to the value of the reigning UTC offset.

drop function if exists report_line(text, timestamp, timestamptz) cascade;
create function report_line(z in text, ts_plain in timestamp, ts_with_tz in timestamptz)
  returns text
  language plpgsql
as $body$
declare
  t constant text not null :=
      rpad(z, 15)||'   '||
      rpad(to_char(ts_plain,   'Dy hh24:mi'        ),  9)||'   '||
      rpad(to_char(ts_with_tz, 'Dy hh24:mi TZH:TZM'), 16);
begin
  return t;
end;
$body$;

Create and execute the timestamptz_vs_plain_timestamp() table function thus:

drop function if exists timestamptz_vs_plain_timestamp() cascade;
create function timestamptz_vs_plain_timestamp()
  returns table(t text)
  language plpgsql
as $body$
declare
  set_timezone      constant text             not null := $$set timezone = '%s'$$;
  tz_on_entry       constant text             not null := current_setting('timezone');

  yyyy              constant int              not null := 2000;
  mm                constant int              not null := 1;
  dd                constant int              not null := 1;
  hh                constant int              not null := 11;
  mi                constant int              not null := 0;
  ss                constant int              not null := 0;
  ts_plain          constant timestamp        not null := make_timestamp  (yyyy, mm, dd, hh, mi, ss);
  ts_with_tz        constant timestamptz      not null := make_timestamptz(yyyy, mm, dd, hh, mi, ss, 'UTC');
  ts_plain_epoch    constant double precision not null := extract(epoch from ts_plain);
  ts_with_tz_epoch  constant double precision not null := extract(epoch from ts_with_tz);
begin
  assert (ts_with_tz_epoch = ts_plain_epoch), 'Assert "ts_with_tz_epoch = ts_plain_epoch" failed';

  t := rpad('Timezone', 15)||'   '||rpad('ts_plain', 9)||'   '||rpad('ts_with_tz',  16);          return next;
  t := rpad('-', 15, '-')  ||'   '||rpad('-',  9, '-') ||'   '||rpad('-', 16, '-');               return next;

  declare
    z                  text   not null := '';
    timezones constant text[] not null := (
                                            select array_agg(name order by utc_offset)
                                            from pg_timezone_names
                                            where name like 'Etc/GMT%'
                                            and utc_offset <> make_interval()
                                          );
  begin
    z := 'UTC';
    execute format(set_timezone, z);
    t := report_line(z, ts_plain, ts_with_tz);                                                    return next;
    t := '';                                                                                      return next;

    foreach z in array timezones loop
      execute format(set_timezone, z);
      declare
        t1              constant double precision not null := extract(epoch from ts_plain);
        t2              constant double precision not null := extract(epoch from ts_with_tz);
        tz_of_timezone  constant interval         not null := utc_offset(z);
        tz_display      constant interval         not null := to_char(ts_with_tz, 'TZH:TZM');
        ts_display      constant timestamp        not null := to_char(ts_with_tz, 'yyyy-mm-dd hh24:mi:ss');
        delta           constant interval         not null := ts_display - ts_plain;
      begin
        assert (t1 = ts_plain_epoch),          'Assert #1 failed';
        assert (t2 = ts_with_tz_epoch),        'Assert #2 failed';
        assert (tz_display = tz_of_timezone), 'Assert #3 failed';
        assert (tz_display = delta),            'Assert #4 failed';
      end;
      t := report_line(z, ts_plain, ts_with_tz);                                                  return next;
    end loop;
  end;

  execute format(set_timezone, tz_on_entry);
end;
$body$;

select t from timestamptz_vs_plain_timestamp();

This is the result:

Timezone          ts_plain    ts_with_tz
---------------   ---------   ----------------
UTC               Sat 11:00   Sat 11:00 +00:00

Etc/GMT+12        Sat 11:00   Fri 23:00 -12:00
Etc/GMT+11        Sat 11:00   Sat 00:00 -11:00
Etc/GMT+10        Sat 11:00   Sat 01:00 -10:00
Etc/GMT+9         Sat 11:00   Sat 02:00 -09:00
Etc/GMT+8         Sat 11:00   Sat 03:00 -08:00
Etc/GMT+7         Sat 11:00   Sat 04:00 -07:00
Etc/GMT+6         Sat 11:00   Sat 05:00 -06:00
Etc/GMT+5         Sat 11:00   Sat 06:00 -05:00
Etc/GMT+4         Sat 11:00   Sat 07:00 -04:00
Etc/GMT+3         Sat 11:00   Sat 08:00 -03:00
Etc/GMT+2         Sat 11:00   Sat 09:00 -02:00
Etc/GMT+1         Sat 11:00   Sat 10:00 -01:00
Etc/GMT-1         Sat 11:00   Sat 12:00 +01:00
Etc/GMT-2         Sat 11:00   Sat 13:00 +02:00
Etc/GMT-3         Sat 11:00   Sat 14:00 +03:00
Etc/GMT-4         Sat 11:00   Sat 15:00 +04:00
Etc/GMT-5         Sat 11:00   Sat 16:00 +05:00
Etc/GMT-6         Sat 11:00   Sat 17:00 +06:00
Etc/GMT-7         Sat 11:00   Sat 18:00 +07:00
Etc/GMT-8         Sat 11:00   Sat 19:00 +08:00
Etc/GMT-9         Sat 11:00   Sat 20:00 +09:00
Etc/GMT-10        Sat 11:00   Sat 21:00 +10:00
Etc/GMT-11        Sat 11:00   Sat 22:00 +11:00
Etc/GMT-12        Sat 11:00   Sat 23:00 +12:00
Etc/GMT-13        Sat 11:00   Sun 00:00 +13:00
Etc/GMT-14        Sat 11:00   Sun 01:00 +14:00

The execution finishes without error, confirming that the four tested assertions hold.

Interpretation and statement of the rules

  • The assert statements confirm that the extract(epoch from timestamptz_value) result is unaffected by the session's timeZone setting. This is, of course, what your intuition tells you to expect.

  • The output confirms that the display of a timestamptz value (formatted as text) is sensitive to the session's timeZone setting and that the text display of a plain timestamp value is not sensitive in this way.

  • The timezone-sensitive formatting of the displayed timestamptz values lines up consistently with what the examples in the previous sections on this page (and on other pages in this overall date-time section) show: informally (as was stated above) that (10 + 1) and (14 - 3) both represent the same value.

  • This is the careful statement of the rule, supported by the fact that all the assert statements succeeded:

    [timestamptz-value] display ◄—
  [internal-timestamp-value + UTC-offset-value-from-session-timezone] display
  annotated with
  [UTC-offset-value-from-session-timezone] display

  • This rule statement lines up with what meeting partners living in the US Pacific coastal region and London, in the winter, understand:

    • The UTC offset for the US Pacific coastal region, in the winter, is minus eight hours.
    • The UTC offset for London, in the winter, is zero hours—in other words, London at that time of year uses the UTC Time Standard.
    • When the local time is, say, 10:00 in the US Pacific coastal region, it's 18:00 in London.
    • Eighteen is ten minus (minus eight).