Re: TIMESTAMP WITH( OUT)? TIME ZONE indexing/type choice... - Mailing list pgsql-general

I've been following this thread, and I am planning a multinational
project. So, will this work correctly? ( I am still waiting for SSH
access to my site to play with Postgres ):

create table test_timestamp(
created timestamp DEFAULT CURRENT_TIMESTAMP::timestamptz NOT NULL
);

Will that be a UTC based timestamp value?


Tom Lane wrote:
>
> Greg Stark <gsstark@mit.edu> writes:
> > The documentation I've read makes it sound like these two data types are
> > equivalent in every way except for the default timezone assumed interpretation
> > when converting to and from textual representations. Is that not true?
>
> I wouldn't think so.  For example, you get dissimilar results near
> daylight-savings-time boundaries:
>
> regression=# select '2003-04-06 01:00'::timestamptz + '3 hours'::interval;
>         ?column?
> ------------------------
>  2003-04-06 05:00:00-04
> (1 row)
>
> regression=# select '2003-04-06 01:00'::timestamp + '3 hours'::interval;
>       ?column?
> ---------------------
>  2003-04-06 04:00:00
> (1 row)
>
> Now in some sense I suppose you could regard this as strictly a matter
> of textual display --- the underlying stored values are indeed three
> hours apart in both cases.  But in practice I think it's a lot easier
> to regard the datatypes as having distinct semantics.  timestamptz
> is "GMT under the hood": it has a definite idea that it is storing an
> absolute time with a universal meaning, which it will translate into
> the local timezone during I/O.  timestamp just stores the nominal
> HH:MM:SS value you give it, with no sense that it knows what time that
> really is, and no attempt to correct for different local timezones nor
> for daylight-savings changes.  The applications of the two types are
> very different.
>
> Because the semantics are in fact different, conversion between
> timestamp and timestamptz is not just a binary-equivalent mapping:
> there is arithmetic happening in here.  And that's why the previous
> suggestion that we could index them interchangeably doesn't fly.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--

Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
                         Sincerely, Dennis Gearon