From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Josh Kupershmidt <schmiddy(at)gmail(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone |
Date: | 2010-09-03 19:24:45 |
Message-ID: | 28048.1283541885@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Josh Kupershmidt <schmiddy(at)gmail(dot)com> writes:
> On Fri, Sep 3, 2010 at 2:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2010-09-03 19:32:43 | Re: How can I use parameters in plain sql |
Previous Message | Josh Kupershmidt | 2010-09-03 19:12:09 | Re: funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone |