Re: Returning timestamp with timezone at specified timezone irrespective of client timezone

On 9/27/20 2:16 PM, aNullValue (Drew Stemen) wrote:
> Hello,
> 
> I've attempted to obtain help with this problem from several other 
> places, but numerous individuals recommended I ask this mailing list.
> 
> What I need is for the ability to return a timestamp with timezone, 
> using the UTC offset that corresponds to a column-defined timezone, 
> irrespective of the client/session configured timezone.
> 
> I have three columns in a table:
> Timezone: 'US/Eastern'
> Date: 2020-10-31
> Time: 08:00
> 
> The output I'm able to find includes these possibilities:
> '2020-10-31 08:00:00'
> '2020-10-31 12:00:00+00'
> 
> Whereas what I actually need is:
> '2020-10-31 08:00:00-05'
> 
> Using the postgresql session-level timezone configuration won't work 
> because I need multiple timezones to be handled in a single set.
> 
> Example code follows. I'm not using to_char in the examples as I likely 
> would in the production code, but I haven't found any way that it could 
> be helpful here regardless.
> 
> ---------------------------------------------------
> 
> SET TIME ZONE 'UTC';
> 
> CREATE TABLE loc
> (
>      id serial not null,
> timezone text not null,
>      loc_date date NOT NULL,
>      loc_time text NOT NULL,
>      CONSTRAINT loc_pkey PRIMARY KEY (id),
>      CONSTRAINT loc_loc_time_check CHECK (loc_time ~ 
> '(^(2[0-3]|[01][0-9]|[0-9]):?([0-5][0-9]):?([0-5][0-9])?$)|(^(1[012]|0[1-9]|[1-9]):[0-5][0-9] 
> [AaPp][Mm]$)'::text)
> )
> ;
> 
> INSERT INTO loc (timezone, loc_date, loc_time) VALUES
> ('US/Eastern', '2020-10-31', '08:00'),
> ('US/Eastern', '2020-11-03', '08:00'),
> ('US/Central', '2020-10-31', '08:00'),
> ('US/Central', '2020-11-03', '08:00');
> 
> SELECT *
> , timezone(l.timezone, l.loc_date + l.loc_time::time without time zone) 
> tswtz
> , (l.loc_date + l.loc_time::time without time zone) tswotz
> FROM loc l
> ORDER BY timezone, loc_date, loc_time
> ;
> 
> ---------------------------------------------------
> 
> id |  timezone  |  loc_date  | loc_time |         tswtz          |       
> tswotz
> ----+------------+------------+----------+------------------------+---------------------
>    7 | US/Central | 2020-10-31 | 08:00    | 2020-10-31 13:00:00+00 | 
> 2020-10-31 08:00:00
>    8 | US/Central | 2020-11-03 | 08:00    | 2020-11-03 14:00:00+00 | 
> 2020-11-03 08:00:00
>    5 | US/Eastern | 2020-10-31 | 08:00    | 2020-10-31 12:00:00+00 | 
> 2020-10-31 08:00:00
>    6 | US/Eastern | 2020-11-03 | 08:00    | 2020-11-03 13:00:00+00 | 
> 2020-11-03 08:00:00
> (4 rows)
> 
> What I actually need is, in example id=7, '2020-10-31 08:00:00-05'.
> 
> Is this even possible? Several people have proposed that I write a 
> custom function to do this on a per-row basis, which... I suppose I can 
> do... I'm just blown away that this isn't something that just works "out 
> of the box".
> 

Something like?:

select '2020-10-31' || ' 08:00 ' || utc_offset from pg_timezone_names 
where name = 'US/Eastern';
           ?column?
----------------------------
  2020-10-31 08:00 -04:00:00


-- 
Adrian Klaver
adrian.klaver@aklaver.com