Skip site navigation (1) Skip section navigation (2)

Re: Some clarification about TIMESTAMP

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: hernan gonzalez <hgonzalez(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Some clarification about TIMESTAMP
Date: 2011-05-31 19:13:14
Message-ID: BANLkTin+5JMiw=9JcROAOq5a4th0yx+3TA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, May 31, 2011 at 10:45 AM, hernan gonzalez <hgonzalez(at)gmail(dot)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.

In response to

pgsql-general by date

Next:From: Merlin MoncureDate: 2011-05-31 19:15:11
Subject: Re: Function Column Expansion Causes Inserts To Fail
Previous:From: hernan gonzalezDate: 2011-05-31 19:00:21
Subject: Re: Some clarification about TIMESTAMP

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group