Re: Re: Getting milliseconds out of TIMESTAMP

"David Wall" <d.wall@computer.org> writes:
> The real question for me is that 7.1 docs say that the resolution of a
> timestamp is 8 bytes at "1 microsecond / 14 digits", yet I generally see
> YYYY-MM-DD HH-MM-SS.cc returned in my queries (both with pgsql and with
> JDBC).

That's just a matter of the default display format not being what you
want.  The underlying representation is double precision seconds from
(IIRC) 1/1/2000, so accuracy is 1 microsec or better for ~70 years
either way from that date, decreasing as you move further out.

One way to get the fractional seconds with better precision is
date_part.  For example,

regression=# create table ts (f1 timestamp);
CREATE
regression=# insert into ts values(now());
INSERT 144944 1
regression=# insert into ts values(now()  + interval '.0001 sec');
INSERT 144945 1
regression=# insert into ts values(now()  + interval '.000001 sec');
INSERT 144946 1
regression=# insert into ts values(now()  + interval '.0000001 sec');
INSERT 144947 1
regression=# select f1, date_part('epoch', f1), date_part('microseconds', f1) from ts;
            f1             |    date_part     |     date_part
---------------------------+------------------+-------------------
 2001-04-22 16:04:31-04    |        987969871 |                 0
 2001-04-22 16:04:39.00-04 |   987969879.0001 |   100.00000000332
 2001-04-22 16:04:45.00-04 | 987969885.000001 | 0.999999997475243
 2001-04-22 16:04:51-04    |        987969891 |                 0
(4 rows)

Not sure why the last example drops out completely --- looks like
something is rounding off sooner than it needs to.  But certainly there
are six fractional digits available at the moment.

            regards, tom lane