Re: Returning timestamp with timezone at specified timezone irrespective of client timezone - Mailing list pgsql-general

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
orless 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
thetimezone is not always known at the time the open/close time (in local time) is captured. There will be some ballot
drop-boxesfor which this will never able to output the timestamp being discussed here, because the jurisdiction hasn't
botheredto 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
regardingthe database. 

Thanks for your help,

Drew

At 2020-09-27T19:37:39-04:00, Greg Smith <ecomputerd@yahoo.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@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. 
> >
> >
>
>