Thread: merge timestamps to intervals

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    up10:14    up10:15    up10:16    down10:17    up10:18
 up
 

I'd like to merge this information to intervals where the service was up
or down.
< intervall, status >10:13-10:15    up10:16-10:16    down10: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

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

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 | UP2008-05-01 10:00:01 | UP2008-05-01 10:00:02 | DOWN2008-05-01
10:00:03| UP2008-05-01 10:00:04 | UP2008-05-01 10:00:05 | DOWN2008-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)
returnssetof 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
SELECTt, 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 | UP2008-05-01
10:00:02| 2008-05-01 10:00:02 | DOWN2008-05-01 10:00:03 | 2008-05-01 10:00:04 | UP2008-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°