Re: storing TZ along timestamps

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: storing TZ along timestamps
Date: 2011-05-27 23:57:56
Message-ID: 4DE03A84.6050304@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 05/27/2011 04:29 PM, Greg Stark wrote:
> On Fri, May 27, 2011 at 4:13 PM, Steve Crawford
> <scrawford(at)pinpointresearch(dot)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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jim Nasby 2011-05-28 00:10:58 Re: storing TZ along timestamps
Previous Message Greg Stark 2011-05-27 23:29:55 Re: storing TZ along timestamps