"Trevor Talbot" <quension@gmail.com> writes:
>> 2) Specific moment in time
>> (i.e. stored in UTC which is unaffected by time zone rules)
>>
>> 3) Specified time of day in specified time zone
>> (equivalent to #2 except when the time zone rules change)
>
>> Surely #2 is a must-have. There has to be a data type for representing a fixed
>> moment in time unaffected by any time zone rules. Anything recording events --
>> which of course occurred at a specific moment in time -- needs it and there
>> are a whole lot of databases which do just that. Actually in my experience
>> most tables have one or sometimes more timestamps of that nature.
>
> While I agree that UTC storage is definitely a needed option, I was
> trying to point out in the scenario above that sometimes an event
> recorded at a specific moment in time *is* local time. Birth
> certificates aren't in UTC. Usually there's no practical difference,
> but there can be a semantic difference.
Thinking of it as UTC is the wrong way to think about it. A birth occurred at
a specific moment in time. You want to record that precise moment, not what it
happened to show on the clock at the time. If the clock turns out to have been
in the wrong timezone the birth isn't going to move.
The use case for storing a local timestamp with a timezone attached is for
things like appointments. If the time zone rules change you would want the
appointment to move with them, not to stay at the same moment in time.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com
