Re: Postgres and timezones

On 01/21/2016 02:58 AM, Steve Rogerson wrote:
> On 20/01/16 19:19, Vik Fearing wrote:
>> On 01/20/2016 04:24 PM, Steve Rogerson wrote:
>>>> Postgres doesn't store original TZ. It does recalculation to local TZ. If you
>>>> need original TZ, you have to store it separetely.
>>>>
>>>
>>> I know and that's what I'm trying to deal with. Given I know the origin TZ  -
>>> as in Europe/Lisbon I'm trying to determine the short name so I can store it.
>>
>> I would recommend against storing the abbreviation.  The abbreviations
>> are not globally unique and don't follow daylight savings.  If you want
>> to store the original time zone, I would use the full name.
>>
>> Something like this might be relative to your interests:
>>
>>      INSERT INTO tbl (ts, tz)two
>>      VALUES ('2016-01-20 00:00', current_setting('TimeZone'));
>>
>> This will do the right thing regardless of where the client is (unless
>> it's set to "localtime" and then it's useless).
>>
>
> That doesn't work for two reasons.
>
> 1. In my application the data comes from several time zones, mostly European
> but also Australia, the US, so the "current_setting" is often inapproriate.
> 2. There are two special times in the year, when the clocks change. The
> awkward one is when the clocks go back. For example this year, for me,
> the 2016-10-27 01:30 happens twice for my current setting, once as BST and
> once as GMT.
>
> We actually store UTC + the offset interval + the short name. The latter being
> mostly for reporting purposes.
>
> The issue is that we can't - from postgres - determine the appropriate short
> name directly.

Probably just me, but I am not sure how if it is stored it cannot be
retrieved?

>
> I can by other means though, say the DateTime module in perl though anything
> that talks to the Olson database on whatever system would do as well.
>
> The only way I can think of doing it is by doing in pg is by the two step (ok
> more that two if I wrap in a transaction or reset the TZ) method :
>
>     # SET TIME ZONE 'Europe/Lisbon';
>     # SELECT to_char('2016-07-20 00:00'::timestamp with time zone , 'TZ');
>      to_char
>     ---------
>      WEST
>     (1 row)
>
> Steve
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com