Re: How to get timezone offset in timestamp with time zone AT TIMEZONE 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.klaver@aklaver.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