Thread: PostgreSQL Timezone and Brazilian DST
Hi! Using PostgreSQL 9.3.10 x86_64 Oracle EL7 compiled by gcc (GCC) 4.8.3 20140911, installed using yum repository. In postgresql.conf, I do have: timezone="America/Sao_Paulo" Since DST is in place in Brazil, it is enough to "systemctl reload postgresql-9.3" to make it effective? Or a complete restart is required? Thanks, -- Atenciosamente, Edson Carlos Ericksson Richter
On 10/27/2015 07:29 PM, Edson Richter wrote: > Hi! > > Using PostgreSQL 9.3.10 x86_64 Oracle EL7 compiled by gcc (GCC) 4.8.3 > 20140911, installed using yum repository. > > In postgresql.conf, I do have: > > timezone="America/Sao_Paulo" > > Since DST is in place in Brazil, it is enough to "systemctl reload > postgresql-9.3" to make it effective? Are you saying that you just changed the timezone setting or that time in Brazil just went from standard time to DST and you want to know whether Postgres will automatically pick that up with the existing setting as above? > Or a complete restart is required? > > Thanks, > -- Adrian Klaver adrian.klaver@aklaver.com
---- Adrian Klaver escreveu ----
> On 10/27/2015 07:29 PM, Edson Richter wrote:
> > Hi!
> >
> > Using PostgreSQL 9.3.10 x86_64 Oracle EL7 compiled by gcc (GCC) 4.8.3
> > 20140911, installed using yum repository.
> >
> > In postgresql.conf, I do have:
> >
> > timezone="America/Sao_Paulo"
> >
> > Since DST is in place in Brazil, it is enough to "systemctl reload
> > postgresql-9.3" to make it effective?
>
> Are you saying that you just changed the timezone setting or that time
> in Brazil just went from standard time to DST and you want to know
> whether Postgres will automatically pick that up with the existing
> setting as above?
Configured since first setup. Brazil went DST few days ago.
Today, executing
Select now()
Resulted in
2015-10-28 02:45:37-03:00
I do expect
2015-10-28 03:45:37-02:00
I suspect that Postgres is ignoring the DST change.
I just want to know if reload or restart is enough to make Postgres get into track.
Thanks,
Edson
>
> > Or a complete restart is required?
> >
> > Thanks,
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@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
On Wed, Oct 28, 2015 at 6:43 AM, Edson Richter <edsonrichter@hotmail.com> wrote: > Configured since first setup. Brazil went DST few days ago. > Today, executing > Select now() > Resulted in > > 2015-10-28 02:45:37-03:00 > > I do expect > > 2015-10-28 03:45:37-02:00 > > I suspect that Postgres is ignoring the DST change. Have you checked the timezone definitions are correct in the server machine? I.e., doing something like this on it: $ TZ="America/Sao_Paulo" date -R Wed, 28 Oct 2015 10:09:19 -0200 $ TZ="America/Sao_Paulo" date -R -d '1 month ago' Mon, 28 Sep 2015 09:09:55 -0300 ( I'm not familiar with your distro, but I got bitten by one of those soem years ago, incorrect timezone definitions ) Francisco Olarte.
On 10/27/2015 10:43 PM, Edson Richter wrote:
> ---- Adrian Klaver escreveu ----
>
> > On 10/27/2015 07:29 PM, Edson Richter wrote:
> > > Hi!
> > >
> > > Using PostgreSQL 9.3.10 x86_64 Oracle EL7 compiled by gcc (GCC) 4.8.3
> > > 20140911, installed using yum repository.
> > >
> > > In postgresql.conf, I do have:
> > >
> > > timezone="America/Sao_Paulo"
> > >
> > > Since DST is in place in Brazil, it is enough to "systemctl reload
> > > postgresql-9.3" to make it effective?
> >
> > Are you saying that you just changed the timezone setting or that time
> > in Brazil just went from standard time to DST and you want to know
> > whether Postgres will automatically pick that up with the existing
> > setting as above?
>
> Configured since first setup. Brazil went DST few days ago.
> Today, executing
>
> Select now()
> Resulted in
>
> 2015-10-28 02:45:37-03:00
>
> I do expect
>
> 2015-10-28 03:45:37-02:00
>
> I suspect that Postgres is ignoring the DST change.
Well it works for me here:
test=> set timezone="America/Sao_Paulo";
SET
test=> select now();
now
-------------------------------
2015-10-28 11:30:21.050485-02
So first thing I would do is see what is actually set in client:
test=> show timezone;
TimeZone
-------------------
America/Sao_Paulo
If it is not what you think it should be then:
test=> select * from pg_settings where name = 'TimeZone';
-[ RECORD 1 ]---------------------------------------------------------------
name | TimeZone
setting | America/Sao_Paulo
unit |
category | Client Connection Defaults / Locale and Formatting
short_desc | Sets the time zone for displaying and interpreting time stamps.
extra_desc |
context | user
vartype | string
source | session
min_val |
max_val |
enumvals |
boot_val | GMT
reset_val | US/Pacific
sourcefile |
sourceline |
Here for more info:
http://www.postgresql.org/docs/9.3/interactive/view-pg-settings.html
In particular, context and source.
>
> I just want to know if reload or restart is enough to make Postgres get
> into track.
No, if the above does not indicate a problem, then the issue is
probably, as Francisco said, in the timezone definitions. The thing is,
you are on 9.3.10 which has the latest time zone data file:
http://www.postgresql.org/docs/9.3/interactive/release-9-3-10.html
Update time zone data files to tzdata release 2015g for DST law changes
in Cayman Islands, Fiji, Moldova, Morocco, Norfolk Island, North Korea,
Turkey, and Uruguay. There is a new zone name America/Fort_Nelson for
the Canadian Northern Rockies.
>
> Thanks,
>
> Edson
>
> >
> > > Or a complete restart is required?
> > >
> > > Thanks,
> > >
> >
> >
> > --
> > Adrian Klaver
> > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org
> <mailto:pgsql-general@postgresql.org>)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> No, if the above does not indicate a problem, then the issue is
> probably, as Francisco said, in the timezone definitions. The thing is,
> you are on 9.3.10 which has the latest time zone data file:
Since OEL is a naked ripoff of Red Hat, I would assume that they configure
Postgres the same way Red Hat does, ie --with-system-tzdata. So what will
matter in that respect is whether your "tzdata" package is up to date, not
which PG version you're running.
However, AFAICT the spring DST transition rule in Brazil hasn't changed
since 2008 or so, so it seems rather unlikely that anybody would have
tzdata old enough for that to be a problem.
I'm betting the OP simply didn't have Postgres' timezone parameter set
properly. Yes, that can be fixed with a reload (as a moment's
experimentation would have shown).
regards, tom lane
On 2015-10-27 20:29, Edson Richter wrote: > Hi! > > Using PostgreSQL 9.3.10 x86_64 Oracle EL7 compiled by gcc (GCC) 4.8.3 > 20140911, installed using yum repository. > > In postgresql.conf, I do have: > > timezone="America/Sao_Paulo" > > Since DST is in place in Brazil, it is enough to "systemctl reload > postgresql-9.3" to make it effective? > Or a complete restart is required? First a side note, if you work with systems and people in more than one time zone, I strongly recommend and it will make your life much simpler if you configure all your servers in UTC (looking at logs, reasoning about automated/cron jobs etc...). Note that you can set the timezone on a per session basis with `set timezone="America/Sao_Paulo"`. Now if you do want to work in local time zone, so America/Sao Paulo in your case: If the timezone is set correctly, we do not need to re-start anything when the DST switch happens. Postgresql will follow the time zone set at the OS level, run the command `timedatectl` to check if your RedHat is set to what you expect. In postgres, use `show timezone` and verify that it says "localtime". If you want postgres to use at a different time zone than the OS, then do configure timezone in postgresql.conf, and yes you will need to restart postgres once you have made that change. -- http://yves.zioup.com gpg: 4096R/32B0F416
Thanks, Tom. I'll play a bit with this in a development server. Regards, Atenciosamente, Edson Carlos Ericksson Richter Em 28/10/2015 12:06, Tom Lane escreveu: > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> No, if the above does not indicate a problem, then the issue is >> probably, as Francisco said, in the timezone definitions. The thing is, >> you are on 9.3.10 which has the latest time zone data file: > Since OEL is a naked ripoff of Red Hat, I would assume that they configure > Postgres the same way Red Hat does, ie --with-system-tzdata. So what will > matter in that respect is whether your "tzdata" package is up to date, not > which PG version you're running. > > However, AFAICT the spring DST transition rule in Brazil hasn't changed > since 2008 or so, so it seems rather unlikely that anybody would have > tzdata old enough for that to be a problem. > > I'm betting the OP simply didn't have Postgres' timezone parameter set > properly. Yes, that can be fixed with a reload (as a moment's > experimentation would have shown). > > regards, tom lane > >
Perfect explanation and indeed useful suggestions. I'll play a bit with a development server. Thanks, Atenciosamente, Edson Carlos Ericksson Richter Em 28/10/2015 12:15, Yves Dorfsman escreveu: > On 2015-10-27 20:29, Edson Richter wrote: >> Hi! >> >> Using PostgreSQL 9.3.10 x86_64 Oracle EL7 compiled by gcc (GCC) 4.8.3 >> 20140911, installed using yum repository. >> >> In postgresql.conf, I do have: >> >> timezone="America/Sao_Paulo" >> >> Since DST is in place in Brazil, it is enough to "systemctl reload >> postgresql-9.3" to make it effective? >> Or a complete restart is required? > First a side note, if you work with systems and people in more than one time > zone, I strongly recommend and it will make your life much simpler if you > configure all your servers in UTC (looking at logs, reasoning about > automated/cron jobs etc...). > > Note that you can set the timezone on a per session basis with `set > timezone="America/Sao_Paulo"`. > > Now if you do want to work in local time zone, so America/Sao Paulo in your case: > > If the timezone is set correctly, we do not need to re-start anything when the > DST switch happens. > > Postgresql will follow the time zone set at the OS level, run the command > `timedatectl` to check if your RedHat is set to what you expect. In postgres, > use `show timezone` and verify that it says "localtime". > > If you want postgres to use at a different time zone than the OS, then do > configure timezone in postgresql.conf, and yes you will need to restart > postgres once you have made that change. > > >