Thread: Convert time to millisec?
I have a statement looking like this...: select sum(Til - Fra) as total from Log_stop where OrdreID = 3434 but I would like the result to be in milliseconds, is this possible? If so, how? (the fields Til and Fra is of type Time) Regards, BTJ -- ----------------------------------------------------------------------------------------------- Bjørn T Johansen btj@havleik.no ----------------------------------------------------------------------------------------------- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" -----------------------------------------------------------------------------------------------
On Mon, Aug 28, 2006 at 10:48:47AM +0200, Bjørn T Johansen wrote: > select sum(Til - Fra) as total from Log_stop where OrdreID = 3434 > > but I would like the result to be in milliseconds, is this possible? If so, how? > (the fields Til and Fra is of type Time) You could use extract(epoch from <interval>) to get the number of seconds (with fractional part) in an interval, then multiply by 1000 to get milliseconds. Example: select extract(epoch from sum(Til - Fra)) * 1000.0 ... -- Michael Fuhr
On Mon, 28 Aug 2006 07:20:02 -0600 Michael Fuhr <mike@fuhr.org> wrote: > On Mon, Aug 28, 2006 at 10:48:47AM +0200, Bjørn T Johansen wrote: > > select sum(Til - Fra) as total from Log_stop where OrdreID = 3434 > > > > but I would like the result to be in milliseconds, is this possible? If so, how? > > (the fields Til and Fra is of type Time) > > You could use extract(epoch from <interval>) to get the number of > seconds (with fractional part) in an interval, then multiply by > 1000 to get milliseconds. Example: > > select extract(epoch from sum(Til - Fra)) * 1000.0 ... > That did the trick... Thx... :) Do you know if this is supported on older versions of PostgreSQL as well? (eg. 7.4.x) BTJ
On Mon, Aug 28, 2006 at 04:18:12PM +0200, Bjørn T Johansen wrote: > On Mon, 28 Aug 2006 07:20:02 -0600 Michael Fuhr <mike@fuhr.org> wrote: > > select extract(epoch from sum(Til - Fra)) * 1000.0 ... > > Do you know if this is supported on older versions of PostgreSQL > as well? (eg. 7.4.x) Yes, it is; I think it's worked since ancient times. See "Date/Time Functions and Operators" in the "Functions and Operators" chapter of the documentation for whatever versions you need. Here's a link for 7.4: http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html -- Michael Fuhr