Обсуждение: Postgres and timezones

Hi, this is wrong:

# select to_char('2016-01-20 00:00'::timestamp at time zone 'Europe/Lisbon',
'TZ');
 to_char
---------
 GMT
(1 row)


It should be WET, "Western European Time". Is there something I'm doing wrong?


Steve

Steve Rogerson wrote:
> Hi, this is wrong:
> 
> # select to_char('2016-01-20 00:00'::timestamp at time zone 'Europe/Lisbon',
> 'TZ');
>  to_char
> ---------
>  GMT
> (1 row)
> 
> 
> It should be WET, "Western European Time". Is there something I'm doing wrong?

That query will always give you your local timezone.

Here in Austria I get:

test=> select to_char('2016-01-20 00:00'::timestamp at time zone 'Asia/Yerevan', 'TZ');
┌─────────┐
│ to_char │
├─────────┤
│ CET     │
└─────────┘
(1 row)

Yours,
Laurenz Albe

On 20/01/16 12:53, Albe Laurenz wrote:
> Steve Rogerson wrote:
>> Hi, this is wrong:
>>
>> # select to_char('2016-01-20 00:00'::timestamp at time zone 'Europe/Lisbon',
>> 'TZ');
>>  to_char
>> ---------
>>  GMT
>> (1 row)
>>
>>
>> It should be WET, "Western European Time". Is there something I'm doing wrong?
>
> That query will always give you your local timezone.
>
> Here in Austria I get:
> us
> test=> select to_char('2016-01-20 00:00'::timestamp at time zone 'Asia/Yerevan', 'TZ');
> ┌─────────┐
> │ to_char │
> ├─────────┤
> │ CET     │
> └─────────┘
> (1 row)
>
> Yours,
> Laurenz Albe
>
That seems odd, but never mind. I'll ask the direct qn then given the above is
it possible to determine the short TZ, say WET in my example.

Thinking about it, probably not as I suspect that pg only stores the offset in
seconds(?) from UTC, so once it has parsed "2016-.... 'Europe/Lisbon'" it has
lost track of the origin TZ and in that case what else could "to_char( ...,
'TZ') mean then other than the current client TZ.

Steve

On 20/01/16 13:27, Pavel Stehule 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 guess I'll have to use something other than pg to do it.

Steve

On 20/01/16 15:38, Steve Crawford wrote:
> Is this of any use?
>
> select * from pg_timezone_names where name = 'Europe/Lisbon';
>     name      | abbrev | utc_offset | is_dst
> ---------------+--------+------------+--------
> Europe/Lisbon | WET    | 00:00:00   | f
>

A bit - but what's missing is to know if daylight savings is in effect. So it
could be WET or WEST so we need a date time and an TZ to work it out.


Steve

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)
    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).
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

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

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