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

On Wed, 2019-09-25 at 10:12 +1000, Paul McGarry wrote:
>
> On Wed, Sep 25, 2019 at 5:44 AM Adrian Klaver <
> adrian(dot)klaver(at)aklaver(dot)com> wrote:

> >
> Andrew's function seems plausible and I need to find some time to
> test it, but I am slightly surprised there isn't a native way to get
> the output, as it seems like something that would be fairly useful
> when dealing with dates.
>
> Perhaps another avenue would be some sort of getOffset function, eg
>
> getOffset('2019-09-25 02:00:00+00','Australia/Sydney')
> that would return +11 (or just 11).
>
> Presumably PostgreSQL must have some internal functionality like that
> because it can do that math on the datetimes, but it doesn't seem to
> be exposed to users.
>
> Thanks all for your input.
>
> Paul
>

You can EXTRACT timezone, timezone_hour and timezone_minute from a
timestamp. Using 'timezone' returns a value representing seconds from
UTC.
Maybe you could just concatenate the date/time with the extracted
offset in your select statement.

HTH,
Robert