Patrick Scharrenberg <[EMAIL PROTECTED]> schrieb: > Hi! > > I have a table where I repeatingly log the status of some service, which > looks something like this: > > < timestamp, status > > > > I'd like to merge this information to intervals where the service was up > or down. > > < intervall, status > > 10:13-10:15 up > 10:16-10:16 down > 10:17-1018 up > > I've no clue how to approach this problem.
I'm sure, there are any ways to do this. For instance this one: First, my table: test=*# select * from status ; t | status ---------------------+-------- 2008-05-01 10:00:00 | UP 2008-05-01 10:00:01 | UP 2008-05-01 10:00:02 | DOWN 2008-05-01 10:00:03 | UP 2008-05-01 10:00:04 | UP 2008-05-01 10:00:05 | DOWN 2008-05-01 10:00:06 | DOWN (7 Zeilen) Now i'm writing a plpgsql-function: create or replace function get_status( OUT t_from timestamp, OUT t_to timestamp, OUT out_status text) returns setof record as $$ declare OLD_STATUS text; OLD_start timestamp; OLD_stop timestamp; REC record; begin OLD_STATUS := NULL; OLD_start := NULL; OLD_stop := NULL; FOR REC in SELECT t, status FROM status ORDER BY t ASC LOOP IF OLD_STATUS isnull THEN OLD_STATUS := REC.status; END IF; IF OLD_start isnull THEN OLD_start := REC.t; END IF; IF OLD_stop isnull THEN OLD_stop := REC.t; END IF; IF OLD_STATUS != REC.status THEN t_from := OLD_start; t_to := OLD_stop; out_status := OLD_status; OLD_STATUS:=REC.status; OLD_start:=REC.t; OLD_stop:=REC.t; RETURN next; END IF; OLD_stop:=REC.t; OLD_STATUS:=REC.status; END LOOP; t_from:=OLD_start; t_to:=OLD_stop; out_status:=REC.status; RETURN next; END; $$ language plpgsql; let's try: test=*# select * from get_status(); t_from | t_to | out_status ---------------------+---------------------+------------ 2008-05-01 10:00:00 | 2008-05-01 10:00:01 | UP 2008-05-01 10:00:02 | 2008-05-01 10:00:02 | DOWN 2008-05-01 10:00:03 | 2008-05-01 10:00:04 | UP 2008-05-01 10:00:05 | 2008-05-01 10:00:06 | DOWN (4 Zeilen) Is this okay for you? 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." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql