On Tue, May 31, 2011 at 10:45 AM, hernan gonzalez <hgonzalez@gmail.com> wrote:
> In this scenario, I assumed the natural convention is: store just a
> UTC time, using a TIMESTAMP. I believe that's the idea
> of a plain TIMESTAMP.
No a plain timestamp has no timezone, UTC or otherwise. it's more
like what you'd use if you had a free pie special from noon to
midnight every wednesday in all your restaurants across the US. A
regular timestamp, no tz info, would say it's from 2011-06-01 12:00:00
to 2011-06-01 23:59:59. This would be noon to midnight in each
timezone on its own. If you converted it from east coast to UTC,
let's say, then it would be from 11 to 11 central, 10 to 10 mountain,
and 9 to 9 west coast time. Folks in hawaii would have to order their
free pie before 6pm. (BTW, this is exactly what one restaurant here
in the US does).
Timestamptz is taken from a given timezone and converted to UTC then
stored as such.