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

>>>>> "Paul" == Paul McGarry <paul@paulmcgarry.com> writes:

 Paul> Hi there,
 Paul> Does anyone have a good way of doing:

 Paul> =====
 Paul> select '2020-04-04 15:00:00+00'::timestamp with time zone AT TIME ZONE
 Paul> 'Australia/Sydney';
 Paul>       timezone
 Paul> ---------------------
 Paul>  2020-04-05 02:00:00

 Paul> select '2020-04-04 16:00:00+00'::timestamp with time zone AT TIME ZONE
 Paul> 'Australia/Sydney';
 Paul>       timezone
 Paul> ---------------------
 Paul>  2020-04-05 02:00:00
 Paul> =====

 Paul> but with the output including the offset, eg:
 Paul> 2020-04-05 02:00:00+11
 Paul> 2020-04-05 02:00:00+10

This is ugly in some respects but minimizes the possible hazards (since
using a function-local SET clause ensures that the timezone is reset on
exit):

create function ts_to_char(t timestamptz, z text) returns text
  language plpgsql immutable
  set timezone = 'GMT'
  as $$
    begin
      perform set_config('timezone', z, true);
      return t::text;
    end;
$$;

select ts_to_char(timestamptz '2020-04-04 16:00:00+00', 'Australia/Sydney');
       ts_to_char       
------------------------
 2020-04-05 02:00:00+10

-- 
Andrew (irc:RhodiumToad)

On 9/23/19 1:32 AM, Paul McGarry wrote:
> Hi there,
> 
> Does anyone have a good way of doing:
> 
> =====
> select '2020-04-04 15:00:00+00'::timestamp with time zone AT TIME ZONE 
> 'Australia/Sydney';
>        timezone
> ---------------------
>   2020-04-05 02:00:00
> 
> select '2020-04-04 16:00:00+00'::timestamp with time zone AT TIME ZONE 
> 'Australia/Sydney';
>        timezone
> ---------------------
>   2020-04-05 02:00:00
> =====
> 
> but with the output including the offset, eg:
> 2020-04-05 02:00:00+11
> 2020-04-05 02:00:00+10
> respectively, so it is clear which 2am it is (the times above are around 
> a DST switch)?
> 
> 
> I have seen a couple of suggestions involving setting the desired time 
> zone in the db session environment, but my actual use case will be a bit 
> more complex, something like,
> 
> ====
> CREATE TABLE users (
> user_id biginit,
> user_timezone text, -- Eg 'Australia/Sydney','Asia/Hong_Kong'
> );
> CREATE TABLE data (
> id bigint,
> user_id bigint,
> datetime timestamp with time zone,
> );
> INSERT INTO users (1,'Australia/Sydney');
> INSERT INTO users (2,'Asia/Hong_Kong');
> INSERT INTO data (5,1,'2020-04-05 02:00:00');
> INSERT INTO data (6,2,'2020-04-05 02:00:00');
> ====
> and I'll want to run a query like:
> ====
> select id, datetime,
>    datetime AT TIME ZONE (select user_timezone from users where 
> data.user_id=users.user_id) as usertime from data;
> ====
> 
> where I want the usertime to be returned in the corresponding users 
> timezone, but with the offset. Therefore whatever renders the offset 
> needs to be capable of doing it per row, independently of the 
> server/session time zone.

This has come up before and the general suggestion has been to have a 
column for a naive(timestamp w/o tz) timestamp and a column for the 
timezone.  You are on the way there, only need to change the type of 
'datetime' field.

> 
> And to_char isn't much help:
> 
> ====
> select to_char('2020-04-04 15:00:00+00'::timestamp with time zone AT 
> TIME ZONE 'Australia/Sydney','YYYY-MM-DD HH24:MI:SSOF');
>          to_char
> ------------------------
>   2020-04-05 02:00:00+00
> ====
>   because to_char only deals with a timestamp and loses the timezone 
> info and you end up with something very wrong.
> 
> Any ideas?
> 
> Thanks for any help.
> 
> Paul


-- 
Adrian Klaver
adrian.klaver@aklaver.com

>>>>> "Adrian" == Adrian Klaver <adrian.klaver@aklaver.com> writes:

 Adrian> This has come up before and the general suggestion has been to
 Adrian> have a column for a naive(timestamp w/o tz) timestamp and a
 Adrian> column for the timezone.

No, that's usually (not always) backwards, and in any event wouldn't
solve this particular issue.

-- 
Andrew (irc:RhodiumToad)

On 9/23/19 7:40 PM, Andrew Gierth wrote:
>>>>>> "Adrian" == Adrian Klaver <adrian.klaver@aklaver.com> writes:
> 
>   Adrian> This has come up before and the general suggestion has been to
>   Adrian> have a column for a naive(timestamp w/o tz) timestamp and a
>   Adrian> column for the timezone.
> 
> No, that's usually (not always) backwards, and in any event wouldn't
> solve this particular issue.
> 

The issue is unclear so I am not sure you can discount this as a 
solution. The OP had:

CREATE TABLE users (
user_id biginit,
user_timezone text, -- Eg 'Australia/Sydney','Asia/Hong_Kong'
);
CREATE TABLE data (
id bigint,
user_id bigint,
datetime timestamp with time zone,
);
INSERT INTO users (1,'Australia/Sydney');
INSERT INTO users (2,'Asia/Hong_Kong');
INSERT INTO data (5,1,'2020-04-05 02:00:00');
INSERT INTO data (6,2,'2020-04-05 02:00:00');

and:

"Therefore whatever renders the offset needs to be capable of doing it 
per row, independently of the server/session time zone."

There is no indication of what the server timezone is set to or where 
the timestamps being assigned to date.datetime are coming from. Do they 
originate as local time(per user) or are they being  generated server side?

-- 
Adrian Klaver
adrian.klaver@aklaver.com

>>>>> "Paul" == Paul McGarry <paul@paulmcgarry.com> writes:

 > On Wed, Sep 25, 2019 at 5:44 AM Adrian Klaver <adrian.klaver@aklaver.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)

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

>>>>> "rob" == rob stone <floriparob@gmail.com> writes:

 rob> You can EXTRACT timezone, timezone_hour and timezone_minute from a
 rob> timestamp. Using 'timezone' returns a value representing seconds
 rob> from UTC.

Yes, but this always outputs a value representing the server timezone;
there's no way (other than the same function set_config trick I gave
above) to make it return a value that represents a different timezone
per row.

-- 
Andrew (irc:RhodiumToad)