--- title: "The plain timestamp and timestamptz data types" url: "https://docs.yugabyte.com/stable/api/ysql/datatypes/type_datetime/date-time-data-types-semantics/type-timestamp/" --- # The plain timestamp and timestamptz data types The semantics of the plain timestamp and timestamptz data types. [YSQL] 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*](/stable/api/ysql/datatypes/type_datetime/timezones/ "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](#interpretation-and-statement-of-the-rules "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](/stable/api/ysql/datatypes/type_datetime/conceptual-background/#wall-clock-time-and-local-time "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). (PostgreSQL, and therefore YSQL, don't support [leap seconds](https://www.timeanddate.com/time/leapseconds.html "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: ```plpgsql 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: ```output 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: ```plpgsql select ('2021-02-14 24:00:00'::timestamp - '2021-02-14 00:00:00'::timestamp)::text as "the interval"; ``` This is the result: ```output the interval -------------- 1 day ``` ## The timestamptz data type A *timestamptz* value represents an [absolute](/stable/api/ysql/datatypes/type_datetime/conceptual-background/#absolute-time-and-the-utc-time-standard "absolute") time. Such a value can therefore be deterministically expressed as the [local time](/stable/api/ysql/datatypes/type_datetime/conceptual-background/#wall-clock-time-and-local-time "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*](/stable/api/ysql/datatypes/type_datetime/timezones/ "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. ```plpgsql 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: ```output 2021-02-14 02:30:35-08 ``` And this is the result of the second query: ```output 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](#the-demonstration "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.) ```plpgsql -- 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: ```plpgsql 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: ```output 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: ```plpgsql set timezone = 'Europe/Amsterdam'; execute qry_mtg; ``` This is Vincent's result: ```output 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: ```plpgsql 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: ```plpgsql 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: ```output 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: ```plpgsql 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: ```output 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: ```plpgsql 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: ```output 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: ```plpgsql 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: ```output 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: ```plpgsql 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: ```output 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: ```plpgsql 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: ```output 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*](/stable/api/ysql/datatypes/type_datetime/timezones/ways-to-spec-offset/name-res-rules/ "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*](/stable/api/ysql/datatypes/type_datetime/timezones/recommendation/ "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](#more-daylight-savings-time-examples "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()*](#timestamptz-vs-plain-timestamp "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()*](/stable/api/ysql/datatypes/type_datetime/timezones/timezone-sensitive-operations/timestamptz-plain-timestamp-conversion/#plain-timestamp-to-from-timestamp-tz "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*](/stable/api/ysql/datatypes/type_datetime/typecasting-between-date-time-values/#text-to-timestamptz "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: ```output 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: ```plpgsql 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*](/stable/api/ysql/datatypes/type_datetime/timezones/extended-timezone-names/canonical-no-country-no-dst/ "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: ```output 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: ```output 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: ```output 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: ```output 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: ```plpgsql 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*. ```plpgsql 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: ```plpgsql 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: ```output 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: ```output [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)*.