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