Thread: Convert time to millisec?

Convert time to millisec?

From
Bjørn T Johansen
Date:
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"
-----------------------------------------------------------------------------------------------

Re: Convert time to millisec?

From
Michael Fuhr
Date:
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

Re: Convert time to millisec?

From
Bjørn T Johansen
Date:
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

Re: Convert time to millisec?

From
Michael Fuhr
Date:
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