Re: How to get timezone offset in timestamp with time zone AT TIME ZONE output.

>>>>> "Paul" == Paul McGarry <paul(at)paulmcgarry(dot)com> writes:

> On Wed, Sep 25, 2019 at 5:44 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>>
>> "Therefore whatever renders the offset needs to be capable of doing
>> it per row, independently of the server/session time zone."

The key point here is that storing the timestamp as WITHOUT rather than
WITH time zone doesn't help you solve the problem of outputting the data
with a different time zone offset shown for different rows. (Since
timestamp without time zone values will be output without an offset,
whereas timestamp with time zone values will be output with the offset
corresponding to the server's timezone, which shouldn't be changing
globally between rows - local changes within a function are OK.)

Paul> Andrew's function seems plausible and I need to find some time to
Paul> test it, but I am slightly surprised there isn't a native way to
Paul> get the output, as it seems like something that would be fairly
Paul> useful when dealing with dates.

The set of functions for dealing with timestamps has, like most of
postgres, largely grown in an unplanned fashion and therefore often has
deficiencies. Also, the fact that we don't follow the spec's semantics
for WITH TIME ZONE (for good reason, the spec can't handle DST
boundaries or historical timezone changes _at all_) complicates choices
of functions and operators to provide.

Possible functions we could add:

strftime('format', t [,timezone]) -- like the C function
to_char(t, 'format', timezone) -- 3-arg version of existing to_char

You can do a getOffset(timestamptz,timezone) function like this:

create function getOffset(t timestamptz, zone text)
returns interval
language sql immutable
as $$
select (t at time zone zone) - (t at time zone 'GMT');
$$;

but formatting the interval result as text is a little more challenging
due to needing explicit + signs:

create function getOffsetStr(t timestamptz, zone text)
returns text
language sql stable
as $$
select regexp_replace(to_char(getOffset(t,zone), 'HH24:MI'),
'^(?!-)', '+');
$$;

--
Andrew (irc:RhodiumToad)