Обсуждение: 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