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@xxxxxxxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx> wrote:
>>>
>>> At 2020-09-27T18:31:49-04:00, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx
>>>>
>>>
>>> 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.
>>>
>>>
>>
>>
>
>
