On Fri, Sep 3, 2010 at 3:24 PM, Tom Lane <tgl@sss.pgh.pa.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