On 7 Feb 2007 at 19:03, Aarni Ruuhimäki wrote: > Hi all, > > Could anyone please tell an easy way to get total hours or minutes from an > interval ? > > SELECT SUM(stop_date_time - start_date_time) AS tot_time FROM work_times WHERE > user_id = 1; > tot_time > ----------------- > 2 days 14:08:44 > > I'd like to have this like ... AS tot_hours ... > tot_hours > ----------------- > 62 > > and ... AS tot_minutes ... > tot_minutes > ----------------- > 3728 > > Maybe even ... AS tot_hours_minutes_seconds > tot_hours_minutes_seconds > ----------------- > 62:08:44 > > > start_date_time and stop_date_time are stored as timestamp without time zone, > using Pg 8.1.5 on CentOs 4.4
select extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05 13:00:00')) as num_seconds; num_seconds ------------ 185040 (1 row) select (extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05 13:00:00')) * interval '1 second') as hours_minutes_seconds; hours_minutes_seconds ----------------------- 51:24:00 (1 row) select extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05 13:00:01'))/60 as minutes; minutes ------------------ 3083.98333333333 (1 row) select round(extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05 13:00:01'))/60) as minutes; minutes --------- 3084 (1 row) select round(extract(epoch from (timestamp '2007-02-07 16:24:00' - timestamp '2007-02-05 13:00:01'))/360) as hours; hours ------- 514 (1 row) ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly