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
[email protected]
