Re: Time functions

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: "Ron St(dot)Pierre" <rstpierre(at)syscor(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Time functions
Date: 2002-12-10 01:31:06
Message-ID: 1039483866.20146.18.camel@linda.lfix.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tue, 2002-12-10 at 00:00, Ron St.Pierre wrote:
> Hi, I've got another question about time - timestamps. I need to
> summarize the amount of time a user has used the system for from a table
> listing the userID, startTimestamp, endTimestamp.
> id userID startTimestamp endTimestamp
> 1 2119 5/10/00 7:32:33 PM 5/10/00 7:33:59 PM
> 2 2119 5/10/00 7:36:30 PM 5/10/00 7:39:27 PM
> 3 2119 5/10/00 7:40:01 PM 5/10/00 9:40:05 PM
> 4 2120 5/10/00 8:11:12 PM 5/10/00 8:11:21 PM
> 5 2121 5/10/00 8:12:26 PM 5/10/00 8:12:46 PM
>
> I don't want to use the interval functions as I don't want the results
> summarized by total months, weeks, days, years, hours, minutes,
> seconds...., I just want the total hours eg 47.98 HOURS
> I couldn't find any suitable way of doing it in the manual or in
> techdocs(.postgresq.org).
>
> Anyone have any suggestions?

Calculate the number of seconds, convert to hours and present with
desired precision:

junk=# SELECT *, TO_CHAR((EXTRACT('epoch' FROM endts) - EXTRACT('epoch'
FROM startts))::NUMERIC/3600, '9999.999') AS hours FROM myt;
id | userid | startts | endts | hours
----+--------+---------------------+---------------------+-----------
1 | 2119 | 2000-10-05 19:32:33 | 2000-10-05 19:33:59 | .024
2 | 2119 | 2000-10-05 19:36:30 | 2000-10-05 19:39:27 | .049
3 | 2119 | 2000-10-05 19:40:01 | 2000-10-05 21:40:05 | 2.001
4 | 2120 | 2000-10-05 20:11:12 | 2000-10-05 20:11:21 | .003
5 | 2121 | 2000-10-05 20:12:26 | 2000-10-05 20:12:46 | .006
(5 rows)


--
Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
LFIX Limited

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Myk Melez 2002-12-10 02:31:56 Re: publicly available PostGreSQL server?
Previous Message Justin Clift 2002-12-10 01:19:25 Re: publicly available PostGreSQL server?