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

If the time zone is not always known, then maybe the time zone field is NULL in that case? Would it be possible to use UTC for datetimes that have a known time zone (and thus specify UTC in the time zone field)? And NULL otherwise? Or is this a case where the datetime comes in without time zone and you have the column there (perhaps configured from elsewhere) to indicate the tz for this inserted time-zone-unaware value? Or maybe it’s the case that you can set the time zone for datetime values you’ve already inserted that, when inserted, didn’t specify a time zone? Sounds like problems any time you need to display a datetime for which you don’t have a time zone. That would be a special case in processing or display. Ugh.

I hope my comments aren’t distracting. I’m just throwing out ideas that might be worth considering.

Greg S.

> On Sep 27, 2020, at 6:51 PM, aNullValue (Drew Stemen) <drew(at)anullvalue(dot)net> wrote:
>
> Yes, unfortunately there's no easy way for me to convert the time to UTC for storage in pg, though I think that's more or less immaterial to the output problem I'm having.
>
> History: the table I'm working on holds the effective open/close hours of ballot drop-box open-for-service hours, and the timezone is not always known at the time the open/close time (in local time) is captured. There will be some ballot drop-boxes for which this will never able to output the timestamp being discussed here, because the jurisdiction hasn't bothered to specify the UTC offset for their drop boxes.
>
> But yes, I understand your points, and in most cases I agree entirely.
>
> There are multiple programming languages being used by multiple teams; I'm personally only working on and knowledgeable regarding the database.
>
> Thanks for your help,
>
> Drew
>
> At 2020-09-27T19:37:39-04:00, Greg Smith <ecomputerd(at)yahoo(dot)com> sent:
>> Is it really a requirement to hold the datetime in the database
>> actually in the specified time zone ? Usual practice is to hold UTC
>> only and convert when necessary to user-configured (or specified) or
>> column-specified time zone perhaps only when transferring to/from the
>> db or when otherwise necessary. Any time zones that have daylight
>> savings will also have a problem when 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(at)anullvalue(dot)net> wrote:
>>>
>>> At 2020-09-27T18:31:49-04:00, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)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.
>>>
>>>
>>
>>
>
>