PostgreSQL Timezone and Brazilian DST

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general