Re: [GENERAL] 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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres and timezones
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres and timezones
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres and timezones
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres and timezones
Right, I was just mentioning the existence of that built-in data in case it was of use to the OP. But I should have also mentioned some caveats in case it is important to his use-case: 1. Full names are not as standardized as one might like so there are multiple possible full names for a time zone, i.e. "America/Los_Angeles", "posix/America/Los_Angeles", "posix/US/Pacific", "PST8PDT", ... 2. Unlike full names, abbreviations do *not* distinctly identify a single time zone. CST is the short name for US Central Standard Time, Cuba, ROC and PRC among others. 3. pg_timezone_names is a *view* and the results for abbreviation and offset change depending on time of year. Right now it's winter on the US West Coast so the abbreviation for "posix/US/Pacific" is PST and I will get results searching for abbreviations matching "PST" but none for "PDT". Come spring, that will change. Cheers, Steve On Wed, Jan 20, 2016 at 7:46 AM, Pavel Stehule wrote: > > > 2016-01-20 16:38 GMT+01:00 Steve Crawford > : > >> 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 >> >> > This is list of know timezones. So if you are searching "abbrev" then you > can find it there. > > > Pavel > > > >> -Steve >> >> On Wed, Jan 20, 2016 at 7:24 AM, Steve Rogerson < >> steve...@yewtc.demon.co.uk> wrote: >> >>> 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 >>> >>> >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >> >
Re: [GENERAL] Postgres and timezones
Hi 2016-01-20 16:24 GMT+01:00 Steve Rogerson : > 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. > probably it isn't possible - the transformation to local zone is immediately after input string is parsed. Regards Pavel > Steve > > >
Re: [GENERAL] Postgres and timezones
2016-01-20 16:38 GMT+01:00 Steve Crawford : > 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 > > This is list of know timezones. So if you are searching "abbrev" then you can find it there. Pavel > -Steve > > On Wed, Jan 20, 2016 at 7:24 AM, Steve Rogerson < > steve...@yewtc.demon.co.uk> wrote: > >> 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 >> >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > >
Re: [GENERAL] Postgres and timezones
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 -Steve On Wed, Jan 20, 2016 at 7:24 AM, Steve Rogerson wrote: > 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 > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Postgres and timezones
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres and timezones
2016-01-20 12:10 GMT+01:00 Steve Rogerson :
> 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?
>
>
Actually your input is now interpretted as being timezone Europe/Lisbon but
to_char still converts it to a string for the timezone that is currently
set on your connection. When I do (my connection is set to
Europe/Amsterdam)
select to_char('2016-01-20 00:00'::timestamp at time zone 'Europe/Lisbon',
'HH:MI TZ');
I get 01:00 CET because it has converted it from Europe/Lisbon to CET.
You can adjust the timezone which is used for display by to_char with
set timezone='Europe/Lisbon';
Regards,
Eelke
Re: [GENERAL] Postgres and timezones
2016-01-20 14:24 GMT+01:00 Steve Rogerson :
> 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.
>
Postgres doesn't store original TZ. It does recalculation to local TZ. If
you need original TZ, you have to store it separetely.
Regards
Pavel
>
> Steve
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] Postgres and timezones
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres and timezones
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
