Re: storing TZ along timestamps

On 05/27/2011 04:29 PM, Greg Stark wrote:
> On Fri, May 27, 2011 at 4:13 PM, Steve Crawford
> <scrawford@pinpointresearch.com>  wrote:
>> I am very interested in the use-case for this (in part as I'm working on a
>> PG related time talk). My experience thus far is that people who want this
>> do not fully understand the nature of date-time calculations and variables
>> in PG.
> The use cases I recall having been mentioned in the past were accurate
> data retention and calendaring applications.
>
> Accurate data retention for things like drug trials need to guarantee
> they retain precisely what the user entered, not an equivalent value.
> If you run a report on a drug trial you need to see that the event was
> recorded as occuring at 1:00pm EST not 6:00pm GMT even if you happen
> to run the report in London.
>
> And calendaring apps want to know what timezone is attached to an
> event, not only the point in time at which it occurs. If your plane
> flight departs at 12:00pm GMT and lands at 2:00pm EST you need to know
> that to book your taxi at 2:30pm EST -- not 7:30pm GMT.
>
> Both of these two cases can be handled differently. The former by
> storing the raw text inputs and then storing the interpreted value as
> a derived column separetly, and the latter by storing the local time
> zone to use for display as an additional attribute along with the
> local address and other attributes of the calendar event.
>
So the proposed change does not handle the first case as you need to 
capture the raw input.

And the second case is already well handled. In fact calendaring is a 
great example. I enter the time for the teleconference and PG nicely 
uses my default timezone to store the point-in-time. When you retrieve 
it, it is shown in your timezone and we both pick up the phone at the 
correct time. And if I know I'll be somewhere else at that time, I just 
ask for the data in that zone. Altering the data type gains nothing.

Cheers,
Steve