pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedTime and microseconds?
3+ messages / 3 participants
[nested] [flat]
* Time and microseconds?
@ 1999-07-01 20:48 Collin F. Lynch <clynch@water2.cs.umass.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Collin F. Lynch @ 1999-07-01 20:48 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: pgsql-hackers@postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] Time and microseconds?
@ 1999-07-04 10:10 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Collin F. Lynch <clynch@water2.cs.umass.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Herouth Maoz @ 1999-07-04 10:10 UTC (permalink / raw)
To: Collin F. Lynch <clynch@water2.cs.umass.edu>; pgsql-interfaces; +Cc: pgsql-hackers@postgreSQL.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] Time and microseconds?
@ 1999-07-04 14:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 1999-07-04 14:18 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: Collin F. Lynch <clynch@water2.cs.umass.edu>; pgsql-interfaces; pgsql-hackers@postgreSQL.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-07-04 14:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-01 20:48 Time and microseconds? Collin F. Lynch <clynch@water2.cs.umass.edu>
1999-07-04 10:10 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-07-04 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox