Thread: Time functions
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? Thanks -- Ron St.Pierre Syscor R&D tel: 250-361-1681 email: rstpierre@syscor.com
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@lfix.co.uk> LFIX Limited
How about: select userID, sum((abstime(endTimeStamp)-abstime(startTimeStamp))::numeric)/3600 as totalhours from yourLogTable group by userID; Cheers, Steve On Monday 09 December 2002 4:00 pm, 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? > Thanks