Richard Huxton <dev@archonet.com> schrieb: > Now (upper_time - lower_time) is the interval you want and summing them > will give you your answer. > > Any help?
Yes, thanks. But, i remember a little function that i wrote in the past: http://a-kretschmer.de/tools/time_intersect.sql And now i have a solution (i hope): test=# select id, status, test-# sum((time_intersect(t_start, t_end, '2006/01/21 00:00:00'::timestamptz, '2006/01/22 23:59:59'::timestamptz)).t2 - test(# (time_intersect(t_start, t_end, '2006/01/21 00:00:00+1'::timestamptz, '2006/01/22 23:59:59+1'::timestamptz)).t1) test-# from status_log test-# where (t_start, t_end) overlaps ('2006/01/21 00:00:00+1'::timestamptz, '2006/01/22 23:59:59'::timestamptz) test-# group by id, status test-# order by 1,2; id | status | sum ----+--------+---------- 1 | 1 | 32:00:00 1 | 2 | 15:59:59 (2 rows) Okay, now i can write a function similar above which returns the interval and the i can sum() this. Thanks, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---------------------------(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
