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

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