Thread: localtime ?

localtime ?

From

James Cloos

Date:

12 June 2015, 01:39:51

On one of my servers, even thopugh everything is in UTC, pg insists on
using timezone -04 by default.

Eg:

,----
| :; date
| Thu Jun 11 22:31:51 UTC 2015
|
| :; psql
| psql (9.4.3, server 9.3.4)
| Type "help" for help.
|
| cloos=# SELECT current_setting('TIMEZONE');
|  current_setting
| -----------------
|  localtime
| (1 row)
|
| cloos=# select now();
|               now
| -------------------------------
|  2015-06-11 18:31:55.597289-04
| (1 row)
`----

But:

,----
| :; grep timezone /etc/postgresql/9.3/main/*
| /etc/postgresql/9.3/main/postgresql.conf:log_timezone = 'UTC'
| /etc/postgresql/9.3/main/postgresql.conf:timezone = 'UTC'
| /etc/postgresql/9.3/main/postgresql.conf:#timezone_abbreviations = 'Default'     # Select the set of available time
zone
| /etc/postgresql/9.3/main/postgresql.conf:                                       # share/timezonesets/.
`----

So how do I convince it always to use utc?

(I did that test w/ ~/.psqlrc moved out of the way, but it does nothing
related to tz.)

Thanks.

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

Re: localtime ?

From

Tom Lane

Date:

12 June 2015, 01:50:25

James Cloos <cloos@jhcloos.com> writes:
> On one of my servers, even thopugh everything is in UTC, pg insists on
> using timezone -04 by default.

> | cloos=# SELECT current_setting('TIMEZONE');
> |  current_setting
> | -----------------
> |  localtime
> | (1 row)

AFAIK, that is not a valid value for timezone, unless someone has stuck a
file by that name into your zoneinfo database directory (which I think is
standard practice on some distros though by no means all).  If so, it
would mean whatever the file said, which would very likely not be UTC.

> But:
> | :; grep timezone /etc/postgresql/9.3/main/*
> | /etc/postgresql/9.3/main/postgresql.conf:log_timezone = 'UTC'
> | /etc/postgresql/9.3/main/postgresql.conf:timezone = 'UTC'

Evidently that grep has little to do with your actual configuration
source.  This would likely be informative as to where "localtime"
is coming from:

    select * from pg_settings where name = 'TimeZone';

            regards, tom lane

Re: localtime ?

From

James Cloos

Date:

12 June 2015, 02:51:06

>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> AFAIK, [localtime] is not a valid value for timezone, unless someone
TL> has stuck a file by that name into your zoneinfo database directory
TL> (which I think is standard practice on some distros though by no
TL> means all).  If so, it would mean whatever the file said, which
TL> would very likely not be UTC.

localtime is also the result is there on all of my debians.  But every
other one defaults to utc.

>> But:
>> | :; grep timezone /etc/postgresql/9.3/main/*
>> | /etc/postgresql/9.3/main/postgresql.conf:log_timezone = 'UTC'
>> | /etc/postgresql/9.3/main/postgresql.conf:timezone = 'UTC'

TL> Evidently that grep has little to do with your actual configuration
TL> source.  This would likely be informative as to where "localtime"
TL> is coming from:

TL>     select * from pg_settings where name = 'TimeZone';

That command says sourcefile is /etc/postgresql/9.3/main/postgresql.conf,
which is what I grep(1)ed.

As it turned out, writing that reminded me that I hadn't run pg_upgradecluster
on that box.  So I did so.  And the 9.4/main cluster defaults to UTC as I
prefer.

The only difference between the results of that pg_settings select
bewteen the two clusters is 9.3 vs 9.4 in the name of the sourcefile.

I restarted the 9.3 cluster to try that select, and it still prefers -04.

Diff(1)ing the /etc/postgresql/9.[34] directories doesn't show any
relevant differences.  Just directory names and the port number.

I'll leave the old cluster stopped but around for a while in case there
are any other queries which might explain the differences.

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

Re: localtime ?

From

Adrian Klaver

Date:

12 June 2015, 02:58:31

On 06/11/2015 04:48 PM, James Cloos wrote:
>>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> TL> AFAIK, [localtime] is not a valid value for timezone, unless someone
> TL> has stuck a file by that name into your zoneinfo database directory
> TL> (which I think is standard practice on some distros though by no
> TL> means all).  If so, it would mean whatever the file said, which
> TL> would very likely not be UTC.
>
> localtime is also the result is there on all of my debians.  But every
> other one defaults to utc.
>
>>> But:
>>> | :; grep timezone /etc/postgresql/9.3/main/*
>>> | /etc/postgresql/9.3/main/postgresql.conf:log_timezone = 'UTC'
>>> | /etc/postgresql/9.3/main/postgresql.conf:timezone = 'UTC'
>
> TL> Evidently that grep has little to do with your actual configuration
> TL> source.  This would likely be informative as to where "localtime"
> TL> is coming from:
>
> TL>     select * from pg_settings where name = 'TimeZone';
>
> That command says sourcefile is /etc/postgresql/9.3/main/postgresql.conf,
> which is what I grep(1)ed.

What does source from 'select * from pg_settings where name =
'TimeZone';' show?

>
> As it turned out, writing that reminded me that I hadn't run pg_upgradecluster
> on that box.  So I did so.  And the 9.4/main cluster defaults to UTC as I
> prefer.
>
> The only difference between the results of that pg_settings select
> bewteen the two clusters is 9.3 vs 9.4 in the name of the sourcefile.
>
> I restarted the 9.3 cluster to try that select, and it still prefers -04.
>
> Diff(1)ing the /etc/postgresql/9.[34] directories doesn't show any
> relevant differences.  Just directory names and the port number.
>
> I'll leave the old cluster stopped but around for a while in case there
> are any other queries which might explain the differences.
>
> -JimC
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: localtime ?

From

James Cloos

Date:

15 June 2015, 21:00:19

The full results on that cluster of select * from pg_settings where name =
'TimeZone' is:

-[ RECORD 1 ]---------------------------------------------------------------
name       | TimeZone
setting    | localtime
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     | configuration file
min_val    |
max_val    |
enumvals   |
boot_val   | GMT
reset_val  | localtime
sourcefile | /etc/postgresql/9.3/main/postgresql.conf
sourceline | 508


The 9.3 vs 9.4 in sourcefile is the only difference between the two
clusters on that box.

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

Re: localtime ?

From

Adrian Klaver

Date:

15 June 2015, 21:25:14

On 06/15/2015 10:55 AM, James Cloos wrote:
> The full results on that cluster of select * from pg_settings where name =
> 'TimeZone' is:
>
> -[ RECORD 1 ]---------------------------------------------------------------
> name       | TimeZone
> setting    | localtime
> 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     | configuration file
> min_val    |
> max_val    |
> enumvals   |
> boot_val   | GMT
> reset_val  | localtime
> sourcefile | /etc/postgresql/9.3/main/postgresql.conf
> sourceline | 508

So what is at line 508 in /etc/postgresql/9.3/main/postgresql.conf?
>
>
> The 9.3 vs 9.4 in sourcefile is the only difference between the two
> clusters on that box.
>
> -JimC
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: localtime ?

From

James Cloos

Date:

15 June 2015, 21:45:47

>>>>> "AK" == Adrian Klaver <adrian.klaver@aklaver.com> writes:

AK> So what is at line 508 in /etc/postgresql/9.3/main/postgresql.conf?

timezone = 'localtime'

That is the same in the 9.4 conf, where everything defaults to utc as I
desire.

Those are Debian's default config files.  As I wrote the diff between
the not working /etc/postgresql/9.3/ snd the working /etc/postgresql/9.4/
is only things like 9.3 vs 9.4 in path names, the port number and the
addition in 9.4 of dynamic_shared_memory_type = mmap.

Whatever caused this is not in the config files.

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

Re: localtime ?

From

Adrian Klaver

Date:

15 June 2015, 22:19:46

On 06/15/2015 11:44 AM, James Cloos wrote:
>>>>>> "AK" == Adrian Klaver <adrian.klaver@aklaver.com> writes:
>
> AK> So what is at line 508 in /etc/postgresql/9.3/main/postgresql.conf?
>
> timezone = 'localtime'
>
> That is the same in the 9.4 conf, where everything defaults to utc as I
> desire.

So from the command line on the non-UTC machine what does the below show:

~>date
>
> Those are Debian's default config files.  As I wrote the diff between
> the not working /etc/postgresql/9.3/ snd the working /etc/postgresql/9.4/
> is only things like 9.3 vs 9.4 in path names, the port number and the
> addition in 9.4 of dynamic_shared_memory_type = mmap.
>
> Whatever caused this is not in the config files.
>
> -JimC
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: localtime ?

From

Oliver Elphick

Date:

15 June 2015, 22:28:30

On 15/06/15 20:44, James Cloos wrote:
>>>>>> "AK" == Adrian Klaver <adrian.klaver@aklaver.com> writes:
>
> AK> So what is at line 508 in /etc/postgresql/9.3/main/postgresql.conf?
>
> timezone = 'localtime'
>
> That is the same in the 9.4 conf, where everything defaults to utc as I
> desire.
>
> Those are Debian's default config files.  As I wrote the diff between
> the not working /etc/postgresql/9.3/ snd the working /etc/postgresql/9.4/
> is only things like 9.3 vs 9.4 in path names, the port number and the
> addition in 9.4 of dynamic_shared_memory_type = mmap.
>
> Whatever caused this is not in the config files.
>

localtime is a timezone but it appears simply to be a copy of the
current local timezone file in /etc/localtime. (This is a binary file
containing a timezone file.)

Is that file different between machines?

You can reconfigure the timezone for the machine:

  sudo dpkg-reconfigure tzdata


Oliver

Re: localtime ?

From

James Cloos

Date:

15 June 2015, 22:38:26

>>>>> "AK" == Adrian Klaver <adrian.klaver@aklaver.com> writes:

AK> So from the command line on the non-UTC machine what does the below show:

AK> ~>date

It is the same machine, just the old 9.3 cluster vs the new 9.4 cluster.

date returns: Mon Jun 15 19:37:14 UTC 2015

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

Re: localtime ?

From

James Cloos

Date:

15 June 2015, 22:43:06

>>>>> "OE" == Oliver Elphick <olly@lfix.co.uk> writes:

OE> localtime is a timezone but it appears simply to be a copy of the
OE> current local timezone file in /etc/localtime. (This is a binary file
OE> containing a timezone file.)

OE> Is that file different between machines?

It is 9.3 vs 9.4 on the same box.

OE> You can reconfigure the timezone for the machine:

OE>   sudo dpkg-reconfigure tzdata

And /etc/localtime is a copy of /usr/share/zoneinfo/Zulu.

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

Re: localtime ?

From

Adrian Klaver

Date:

15 June 2015, 22:55:44

On 06/15/2015 11:44 AM, James Cloos wrote:
>>>>>> "AK" == Adrian Klaver <adrian.klaver@aklaver.com> writes:
>
> AK> So what is at line 508 in /etc/postgresql/9.3/main/postgresql.conf?
>
> timezone = 'localtime'
>
> That is the same in the 9.4 conf, where everything defaults to utc as I
> desire.

Went back to the beginning of the thread:

http://www.postgresql.org/message-id/m3616t3m5d.fsf@carbon.jhcloos.org

where you had:

"
But:

,----
| :; grep timezone /etc/postgresql/9.3/main/*
| /etc/postgresql/9.3/main/postgresql.conf:log_timezone = 'UTC'
| /etc/postgresql/9.3/main/postgresql.conf:timezone = 'UTC'
| /etc/postgresql/9.3/main/postgresql.conf:#timezone_abbreviations =
'Default'     # Select the set of available time zone
| /etc/postgresql/9.3/main/postgresql.conf:
           # share/timezonesets/.
"

So now I am lost:(

Also what is the timezone set to in the 9.4 cluster?


If nothing else, just change the timezone in the 9.3 cluster to 'UTC'.

>
> Those are Debian's default config files.  As I wrote the diff between
> the not working /etc/postgresql/9.3/ snd the working /etc/postgresql/9.4/
> is only things like 9.3 vs 9.4 in path names, the port number and the
> addition in 9.4 of dynamic_shared_memory_type = mmap.
>
> Whatever caused this is not in the config files.
>
> -JimC
>


--
Adrian Klaver
adrian.klaver@aklaver.com