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

On Fri, Sep 3, 2010 at 3:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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)

EXTRACT(epoch ...) was what I was looking for:

SELECT EXTRACT(epoch FROM ts - '1999-12-31 19:00:00-05'::timestamptz)
FROM timestamps_test LIMIT 5;
date_part
-----------------------
1.4120666068199e-309
1.4154982781624e-309
1.41550281692099e-309
1.41591466059161e-309
1.41591524669472e-309
(5 rows)

Thanks for the help, Tom.

Josh