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