Re: Storage sizes for dates/times (documentation bug?) - Mailing list pgsql-general

From Karsten Hilbert
Subject Re: Storage sizes for dates/times (documentation bug?)
Date
Msg-id 20080416150956.GC4138@merkur.hilbert.loc
Whole thread Raw
In response to Re: Storage sizes for dates/times (documentation bug?)  (Sam Mason <sam@samason.me.uk>)
Responses Re: Storage sizes for dates/times (documentation bug?)  (Sam Mason <sam@samason.me.uk>)
List pgsql-general
On Tue, Apr 15, 2008 at 03:31:46PM +0100, Sam Mason wrote:

> But I was under the impression that you didn't want any time zone
> information.
Wrong impression.

> You wanted to know that that an appointment was at 3PM at
> the patients local time,
... plus "what does local time mean".

> attempting to correct this for the local time
> zone of any analyst is invalid.
Sure, there can be valid and invalid normalisations.

> I must be missing something then, can you explain why the original time
> zone matters?

a) I want to be able to display when a patient's appointment
   happened in local time.

b) I must be able to aggregate appointments from different
   time zones into a coherent EMR. For that I need to be able
   to map them onto, say, UTC.

Taken together this could be served nicely by a UTC-storing,
local-tz-remembering timestamp.

> If you actually hardcoded your timezone as
> GMT+6, or whatever, then yes it may be different.  But only if you went
> around at midnight March 31st, changing computers to be GMT+5
The machines do that by themselves.

> In some cases yes I'd agree, but I have a feeling the number of cases is
> surprisingly small in practise.
The sampling may not be that large but when the problem is
there it is painful.

Basically, akin to "there's no such thing as plain text"
there should be "there's no such thing as a timezone-less
timestamp".

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

pgsql-general by date:

Previous
From: "Jimmy Choi"
Date:
Subject: "vacuum" and "cluster"
Next
From: Karsten Hilbert
Date:
Subject: Re: Storage sizes for dates/times (documentation bug?)