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
