Re: Re: Getting milliseconds out of TIMESTAMP - Mailing list pgsql-general

> "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.

That makes sense, but it wasn't clear if there was a clean way to get the
extra info.  Your example below looks interesting, but I'll need to look
further to see if I understand what you are really doing.  The real key for
me will be to come up with a generic mechanism that is easy to plug into
JDBC.

> 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.

This solution appears to show that the data is there, but that extracting it
is not that pleasant, especially in an automated way from JDBC.  It seems
like we'd have to modify select calls such that whenever a timestamp field
is being used, we'd get the timestamp, but also get the 'date_part(epoch)',
then put use the numbers after the decimal point in epoch and replace any
numbers after the period in the timestamp.

If I have to change my code to make it work, then I'd prefer to simply store
the 64-bit long integer in the database and save all of the date conversions
and parsing.  But when it's a TIMESTAMP, the data looks a lot better when
using psql, and it makes queries by date ranges usable by mortals!

Thanks,
David