Обсуждение: Time and microseconds?

Greetings: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?  Is there some style variable that must be
set,or have I simply missed something in 
 
the documentation?Thanks for your help in advance.Collin Lynch.

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