Обсуждение: Returning timestamp with timezone at specified timezone irrespective of client timezone

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

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,
andnot as of the date/time values in the row. 

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".

"aNullValue (Drew Stemen)" <drew@anullvalue.net> writes:
> What I need is for the ability to return a timestamp with timezone, using the UTC offset that corresponds to a
column-definedtimezone, irrespective of the client/session configured timezone. 

I might be confused, but I think that the way to get the timestamptz
values you want is

# SELECT *
, ((l.loc_date || ' ' || l.loc_time)::timestamp) at time zone timezone tswtz
FROM loc l
ORDER BY timezone, loc_date, loc_time
;
 id |  timezone  |  loc_date  | loc_time |         tswtz
----+------------+------------+----------+------------------------
  3 | US/Central | 2020-10-31 | 08:00    | 2020-10-31 13:00:00+00
  4 | US/Central | 2020-11-03 | 08:00    | 2020-11-03 14:00:00+00
  1 | US/Eastern | 2020-10-31 | 08:00    | 2020-10-31 12:00:00+00
  2 | US/Eastern | 2020-11-03 | 08:00    | 2020-11-03 13:00:00+00
(4 rows)

These are the correct timestamptz values, as displayed with
the session timezone set to UTC as per your example.  If what
you're asking for is that the *presentation* vary per the timezone
column, then you have to fake it, because timestamptz_out simply
will not do that for you.  However, it's not very clear to me
why you don't just concatenate the loc_date, loc_time, and timezone
columns if that's the presentation you want.

Alternatively, if this was just a dummy example and you really
mean you've done a timestamptz calculation and now want to present
it in a varying timezone, you could do something like this,
using now() as a placeholder for some timestamptz expression:

# select timezone, now(), (now() at time zone timezone) || ' ' || timezone tswtz from loc l;
  timezone  |              now              |                 tswtz
------------+-------------------------------+---------------------------------------
 US/Eastern | 2020-09-27 23:32:19.321202+00 | 2020-09-27 19:32:19.321202 US/Eastern
 US/Eastern | 2020-09-27 23:32:19.321202+00 | 2020-09-27 19:32:19.321202 US/Eastern
 US/Central | 2020-09-27 23:32:19.321202+00 | 2020-09-27 18:32:19.321202 US/Central
 US/Central | 2020-09-27 23:32:19.321202+00 | 2020-09-27 18:32:19.321202 US/Central
(4 rows)

The key thing to understand here is that AT TIME ZONE either
rotates from local time to UTC, or vice versa, depending on
whether its input is timestamp or timestamptz.

            regards, tom lane

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. 
>
>

On 9/27/20 4: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.

[snip]

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".

Are you really asking what the TZ offset was on a specific date (Like DST or not)?

--

Angular momentum makes the world go 'round.

On 9/27/20 16:13, Ron wrote:
> On 9/27/20 4:16 PM, aNullValue (Drew Stemen) wrote:
>> 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.
> 
> Are you really asking what the TZ offset was on a specific date (Like
> DST or not)?

IIUC, there is a gap here in PostgreSQL. i think it could most
easily/quickly be addressed with an overloaded version of to_char that
accepts a "display timezone" for its timestamp to character conversion.

FWIW - in Oracle this is handled by having two different data types:
1) TIMESTAMP WITH TIME ZONE
2) TIMESTAMP WITH LOCAL TIME ZONE

ironically, oracle's "local" data type is the equivalent PostgreSQL's
timestamp with time zone where the timestamp is converted and
processed/stored without a time zone. afaik postgresql doesn't have a
native data type equivalent to the first variant in oracle, which
actually considers the time zone as part of the data. (am i missing
something?)

in lieu of having built-in support, a PL/pgSQL function to set the
session-level timezone in between processing each record is the best
approach i've thought of so far.

-Jeremy

-- 
http://about.me/jeremy_schneider

At 2020-09-27T19:36:34-04:00, Tom Lane <tgl@sss.pgh.pa.us> sent:
> "aNullValue (Drew Stemen)" <drew@anullvalue.net> writes:
> > What I need is for the ability to return a timestamp with timezone, using the UTC offset that corresponds to a
column-definedtimezone, irrespective of the client/session configured timezone.
 
> 
> I might be confused, but I think that the way to get the timestamptz
> values you want is
> 
> # SELECT *
> , ((l.loc_date || ' ' || l.loc_time)::timestamp) at time zone timezone tswtz
> FROM loc l
> ORDER BY timezone, loc_date, loc_time
> ;
>  id |  timezone  |  loc_date  | loc_time |         tswtz          
> ----+------------+------------+----------+------------------------
>   3 | US/Central | 2020-10-31 | 08:00    | 2020-10-31 13:00:00+00
>   4 | US/Central | 2020-11-03 | 08:00    | 2020-11-03 14:00:00+00
>   1 | US/Eastern | 2020-10-31 | 08:00    | 2020-10-31 12:00:00+00
>   2 | US/Eastern | 2020-11-03 | 08:00    | 2020-11-03 13:00:00+00
> (4 rows)
> 
> These are the correct timestamptz values, as displayed with
> the session timezone set to UTC as per your example.  If what
> you're asking for is that the *presentation* vary per the timezone
> column, then you have to fake it, because timestamptz_out simply
> will not do that for you.  However, it's not very clear to me
> why you don't just concatenate the loc_date, loc_time, and timezone
> columns if that's the presentation you want.
> 
> Alternatively, if this was just a dummy example and you really
> mean you've done a timestamptz calculation and now want to present
> it in a varying timezone, you could do something like this,
> using now() as a placeholder for some timestamptz expression:
> 
> # select timezone, now(), (now() at time zone timezone) || ' ' || 
> timezone tswtz from loc l;
>   timezone  |              now              |                 tswtz     
>             
> ------------+-------------------------------+---------------------------------------
>  US/Eastern | 2020-09-27 23:32:19.321202+00 | 2020-09-27 
> 19:32:19.321202 US/Eastern
>  US/Eastern | 2020-09-27 23:32:19.321202+00 | 2020-09-27 
> 19:32:19.321202 US/Eastern
>  US/Central | 2020-09-27 23:32:19.321202+00 | 2020-09-27 
> 18:32:19.321202 US/Central
>  US/Central | 2020-09-27 23:32:19.321202+00 | 2020-09-27 
> 18:32:19.321202 US/Central
> (4 rows)
> 
> The key thing to understand here is that AT TIME ZONE either
> rotates from local time to UTC, or vice versa, depending on
> whether its input is timestamp or timestamptz.
> 
>             regards, tom lane
>

I should have been much more clear about this ultimately being converted to text for output. I didn't simply because
thedefault-rendering of the timestamptz column provided format identical to the character format I'll ultimately be
using.Apologies for my lack of clarity. 
 

I cannot simply append because then I'd wind up with your example, where 'US/Eastern' is appended to the computed
string;I need that to be the actual UTC offset ('-04:00:00' or '-05:00:00', rather than the string 'US/Eastern').  The
desiredresult is '2020-11-03 18:12:34-05:00:00', where '-05:00:00' is the effective UTC offset in the timezone
specifiedby its name in another column. 
 

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. 
> >
> >
>
>

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
UTCfor datetimes that have a known time zone (and thus specify UTC in the time zone field)? And NULL otherwise? Or is
thisa 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
timezone for datetime values you’ve already inserted that, when inserted, didn’t specify a time zone? Sounds like
problemsany time you need to display a datetime for which you don’t have a time zone. That would be a special case in
processingor 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@anullvalue.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
moreor 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
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
knowledgeableregarding the 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. 
>>>
>>>
>>
>>
>
>

On 9/27/20 16:42, Jeremy Schneider wrote:
> On 9/27/20 16:13, Ron wrote:
>> On 9/27/20 4:16 PM, aNullValue (Drew Stemen) wrote:
>>> 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.
>>
>> Are you really asking what the TZ offset was on a specific date (Like
>> DST or not)?
> 
> in lieu of having built-in support, a PL/pgSQL function to set the
> session-level timezone in between processing each record is the best
> approach i've thought of so far
FYI, here's the hack approach I was thinking of.

I intentionally didn't preserve the session's timezone in the
transaction, but that could easily be done with a few more lines of
PL/pgSQL.


create or replace function to_char(
   v_tstz timestamp with time zone
  ,v_format text
  ,v_tz text
) returns text language plpgsql
immutable parallel safe
as $$
begin
  perform set_config('timezone',v_tz,true);
  return to_char(v_tstz,v_format);
end;
$$
;

SELECT
     id
    ,to_char(l.loc_date+l.loc_time::time
             ,'YYYY-MM-DD HH24:MI:SSOF'
             ,timezone) tsw
FROM loc l
ORDER BY timezone, loc_date, loc_time
;

 id |          tsw
----+------------------------
  3 | 2020-10-31 03:00:00-05
  4 | 2020-11-03 08:00:00-06
  1 | 2020-10-31 09:00:00-04
  2 | 2020-11-03 08:00:00-05


https://gist.github.com/aNullValue/ba838d6b40495695df0daa11c2748248


-- 
http://about.me/jeremy_schneider

On 9/27/20 20:13, Jeremy Schneider wrote:
> create or replace function to_char(
>    v_tstz timestamp with time zone
>   ,v_format text
>   ,v_tz text
> ) returns text language plpgsql
> immutable parallel safe
> as $$
> begin
>   perform set_config('timezone',v_tz,true);
>   return to_char(v_tstz,v_format);
> end;
> $$
> ;


Just occurred to me, I don't know if this is actually parallel safe. I'm
not sure how transaction-level session configuration is handled inside
parallel workers. Might be best to leave off the "parallel safe" flag
from the function for now.

-J

-- 
http://about.me/jeremy_schneider