SEO

Hi!

I have a table where I repeatingly log the status of some service, which
looks something like this:

< timestamp, status >

Now, everytime my service is up I save the timestamp and a status of
"up", if it's down I save the timestamp with "down", eg:
10:13 up
10:14 up
10:15 up
10:16 down
10:17 up
10:18 up

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.

Any ideas/hints?
Also suggestions on a feasible better schema are welcome. :-)

Thanks
Patrick

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Patrick Scharrenberg wrote:

> 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.

About 12 hours ago there was a conversation in pgsql-sql with subject
"Difference in columns" that included examples that can be trivially
adapted to your problem.

--
Craig Ringer

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Patrick Scharrenberg <pittipatti@web.de> 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