Re: funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone

Josh Kupershmidt <schmiddy@gmail.com> writes:
> On Fri, Sep 3, 2010 at 2:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm thinking some of them might be different by submicrosecond amounts.

> Ah yes, this is likely why. pg_config says CONFIGURE = ...
> '--disable-integer-datetimes' ...

> But I'm having trouble seeing for sure whether there are
> submicrosecond parts of these timestamps.

Experimenting, I can do this:

regression=# create table t1 (ts timestamptz);
CREATE TABLE
regression=# insert into t1 select '1999-12-31 19:00:00.0000001-05'::timestamptz;
INSERT 0 1
regression=# insert into t1 select '1999-12-31 19:00:00.000000-05'::timestamptz;
INSERT 0 1
regression=# select * from t1;
           ts
------------------------
 1999-12-31 19:00:00-05
 1999-12-31 19:00:00-05
(2 rows)

regression=# select extract(epoch from ts - '1999-12-31 19:00:00-05'::timestamptz) from t1;
      date_part
----------------------
 1.00000761449337e-07
                    0
(2 rows)

This timestamp (2000-01-01 00:00 GMT) is actually the zero value
internally for Postgres timestamps, so in principle a float timestamp
has precision far smaller than microseconds for values near this.
We don't make any great effort to expose that though.  It looks like
the closest value that timestamptzin makes different from zero is

regression=# select extract(epoch from '1999-12-31 19:00:00.00000000001-05' - '1999-12-31 19:00:00-05'::timestamptz) ;
      date_part
----------------------
 1.45519152283669e-11
(1 row)

            regards, tom lane