On 16/12/12 18:23, Terence Ferraro wrote:
On Sat, Dec 15, 2012 at 11:54 PM, Gavin Flower
<gavinflo...@archidevsys.co.nz <mailto:gavinflo...@archidevsys.co.nz>>
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
<gavinflo...@archidevsys.co.nz
<mailto:gavinflo...@archidevsys.co.nz>> 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, thenmy 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! :-)
