Re: Re: Getting milliseconds out of TIMESTAMP

"David Wall" <d(dot)wall(at)computer(dot)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