|
On 16/12/12 18:23, Terence Ferraro
wrote:
On Sat, Dec 15, 2012 at 11:54 PM, Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx>
wrote:
Please do not top post, see end of email for rest of
reply...
(Bottom posting is the convention here, so people can see
the context before reading your reply.)
On 16/12/12 16:52, Terence Ferraro wrote:
Sorry about the double post, I
thought the list disallowed attachments so I sent it
again with a pastebin link instead of an attachment.
This change does not affect the storage at all. If it
did, pre-9.1 things would have been a mess. Rather,
this allows the system to determine the timezone for
localized use at runtime.
Post 9.1, the system determines this via initdb data
directory initialization and automatically sets it
within postgresql.conf.
In other words, the default now is *not* GMT but
rather the system detected timezone at initdb runtime.
Removing that statically set configuration option, in
this case, *then* assumes GMT.
In other words, it may be 2 AM NZST, but would you
really want to walk around and have all of your clocks
read 1 PM (GMT)? :)
T.J.
On Sat, Dec 15, 2012 at 10:35
PM, Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx>
wrote:
On 16/12/12 16:07, Terence Ferraro
wrote:
We recently began
upgrading our clients' servers from 9.0
-> 9.2. After a few deployments and a
little digging we noticed that 9.0 ->
9.1 broke the use of no timezone set
within postgresql.conf. That is, not
setting the option was now defaulting to
GMT instead of the system timezone.
Unfortunately, this put quite a damper on
our "one configuration file to rule them
all" setup across servers located
throughout various time zones.
I obtained the commit
ca4af308c32d03db5fbacb54d6e583ceb904f268
from the git repository and have
reversed it against 9.2. Though it
didn't apply as smoothly as initially
hoped; applying a few of the failed
hunks manually allowed me to
successfully compile 9.2.1 with these
changes reversed.
After some (light) testing, the previous
functionality of the system choosing the
timezone at runtime seems to be
functional again.
I found this functionality invaluable
and figured I'd get the patch ( http://pastebin.com/5AyaX2RF)
posted in case anyone else ever needs
this functionality back. It works
against 9.2.1; no guarantees on future
releases of course and YMMV.
T.J.
I simply do not
understand why you would
_NOT_ want to store
date/times as GMT!
Storing as GMT, allows
the times to be easily converted
into whatever time zone you are in,
also allows for times to be correctly ordered irrespective of time
zone.
If I make a phone
call from Auckland to New York
at 2am NZST on Friday, then my colleague
is talking to me at the same time
- even though it is still Thursday
for them!
Cheers,
Gavin
My machine time is stored in GMT, but displayed in local
time. If I store time as the local time zone, then I could
end up with a file created after I had amended it, or having
a negative uptime! Similarly, I want my date/times to be
stored in GMT, but displayed in local time.
This is similar to storing money as an integer value of
cents then formatting it for output with 2 decimal place.
Storing money as a double, is a common programming error -
though, sometimes storing money as a double is actually
valid.
The key point is the storage type used should be one most
appropriate for processing, and need not be what is
displayed. In a database, someone's name will normally be
stored in 2 or fields, but printed out as if it had been
stored as a single string.
Cheers,
Gavin
All of your points are correct. The patch I provided changes none
of that.
I think what you're missing here is that the timezone option in
postgresql.conf does not change the internals of how a timestamp
is stored. Rather, without it, by leaving that option blank, you
would NOT see your local time when running SELECT
CURRENT_TIMESTAMP. Instead, you'd see the GMT time.
Likewise, if I created a configuration file on my local machine,
initdb would set the timezone option to "US/Eastern" and if I then
distributed that to other servers located in various timezones
they would ALL display CURRENT_TIMESTAMP with reference to
"US/Eastern" instead of the actual machine timezone.
Previously (and now, with the patch), leaving the timezone option
blank would simply determine, at run time, the machine's time zone
and display times in that local timezone.
Does that make sense? The timezone parameter has nothing to do
with storage types and everything to do with the display of the
local time.
I had a horrible feeling, I was
missing something obvious! :-)
|
