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

Is it really a requirement to hold the datetime in the database actually in the specified time zone ? Usual practice is
tohold UTC only and convert when necessary to user-configured (or specified) or column-specified time zone perhaps only
whentransferring to/from the db or when otherwise necessary. Any time zones that have daylight savings will also have a
problemwhen calculating datetime differences when crossing the daylight savings boundary. UTC doesn’t have this
problem. 

Can you refactor to only store UTC and the desired time zone, then convert to that time zone when needed?

Also, what programming language outside of SQL are you using (if any)?

Greg S.

> On Sep 27, 2020, at 5:39 PM, aNullValue (Drew Stemen) <drew@anullvalue.net> wrote:
>
> At 2020-09-27T18:31:49-04:00, Adrian Klaver <adrian.klaver@aklaver.com> sent:
>>> 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
>>
>
> The problem there is that the value of utc_offset in pg_timezone_names is correct only as of the current point in
time,and not as of the date/time values in the row. 
>
>