Time and microseconds?

At 23:48 +0300 on 01/07/1999, Collin F. Lynch wrote:

I'm having the peculiar problem That Postgres Seems unable to deal
with microseconds in a time field. I have the field defined as type time,
and postgres accepts input of the form 12:22:13.41 but appears to ose the
microseconds withn the database itself?

It's just the output of the time datatype. This datatype is a bit odd. You
can see that the microseconds are kept. Compare:

testing=> select '12:05:11.04'::time;
?column?
--------
12:05:11
(1 row)

testing=> select datetime( 'today', '12:05:11.04'::time );
datetime
-------------------------------
Sun Jul 04 12:05:11.04 1999 IDT
(1 row)

So you see, the milliseconds are actually. It's just that it didn't show in
the normal display form.

The TIME datatype is supposed to be compatible with SQL92, but it's, well,
not exactly. Anyway, the default precision for time is 0, that is, no
fractions of seconds unless stated so explicitly. However, since it stores
milliseconds, it defies that definition.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Herouth Maoz <herouth@oumail.openu.ac.il> writes:

The TIME datatype is supposed to be compatible with SQL92, but it's, well,
not exactly. Anyway, the default precision for time is 0, that is, no
fractions of seconds unless stated so explicitly. However, since it stores
milliseconds, it defies that definition.

Actually, datetime uses a float8 to store seconds-since-some-epoch-
or-other (from a quick look at the sources, it looks like datetime 0
is midnight GMT 1/1/2000). That means the precision varies depending
on how far away from time zero you are talking about. Currently,
with less than 16 million seconds left until the epoch, a standard
IEEE float8 will have about 28 bits to spare to the right of the
binary point, giving us nominal precision not much worse than
nanoseconds. For a more reasonable time range, say up to 100 years
from the epoch, you could expect microsecond precision.

The default output routine for type datetime doesn't seem to want
to print more than 2 digits after the decimal point, but you can extract
the full fractional precision with datetime_part("second", ...).

regards, tom lane