time + date_part oddness?
I am writing an article of PostgreSQL 7.1 and have some questions.
I can get the millisecond part of a timestamp data using date_part.
test=# SELECT date_part('millisecond','2001/1/15 12:04:05.1234'::TIMESTAMP);
date_part
-----------
123.4
(1 row)
However if I apply the function to a time data, I get a strange result.
test=# SELECT date_part('millisecond','12:04:05.1234'::time);
date_part
------------------
5123.39999999676
(1 row)
Any thought?
(This is current)
--
Tatsuo Ishii
Any thought?
Hmm. It is not consistantly implemented :(
timestamp_part() uses just the fractional part of the seconds to
calculate milliseconds. But interval_part() uses both fractional and
integer parts to figure the result, and that is the routine which ends
up being used for the time value.
I'd assume that it should be using fractional seconds only, just like
timestamp_part() does. Any reason not to change it for 7.1?
btw, what should 'microseconds' return? It suffers from the problems
mentioned already, plus leaves the "milliseconds" part in the result.
That should probably only return the pieces which are less than a
millisecond...
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
I'd assume that it should be using fractional seconds only, just like
timestamp_part() does. Any reason not to change it for 7.1?
Agreed.
btw, what should 'microseconds' return? It suffers from the problems
mentioned already, plus leaves the "milliseconds" part in the result.
That should probably only return the pieces which are less than a
millisecond...
Hm. I'd venture to disagree. People are used to breaking down time
into hours-minutes-seconds, but I never heard of anyone expressing
a measurement as so many milliseconds plus so many microseconds.
I'd vote for making 'milliseconds' produce 'fractional second times 10^3'
and 'microseconds' produce 'fractional second times 10^6'. You wouldn't
use both together, just whichever seemed appropriate for the precision
of your data.
regards, tom lane
PS: "fourpalms.org" ?
PS: "fourpalms.org" ?
Yup. I finally got DSL at home, and in the 4 months between ordering and
installation thelockharts.org plus some other candidates (including the
names of every animal in the household, and we have lots!) had vanished.
I'll explain the significance of the name over beers the next time we
get together ;) In the meantime, I haven't quite figured out how to
start decoupling my various projects from my long-standing
lockhart@alumni.caltech.edu address, so that one may reappear as a
preferred address for some things...
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
In the meantime, I haven't quite figured out how to
start decoupling my various projects from my long-standing
lockhart@alumni.caltech.edu address,
I'm not even trying ... I don't work for SSS anymore, but I'll be
tgl@sss.pgh.pa.us for the foreseeable future.
regards, tom lane