Page 1 of 1

Quick check of feed latency

Posted: 08 Apr 2016, 19:00
by disconn3ct
Cleanup suggestions welcome, but this seems to work. It will show the update latency of all feeds for any feed that is more than 15 minutes out of date. It doesn't handle pulling down the default update, so if update_interval is 0 it just assumes 0. (Oh, and it filters out update_interval greater than 10,000. Because I felt like it.)

Code: Select all

-- update_interval + 15 shows feeds 15 or more minutes out of date
-- inner select shows all feeds with their update interval and latency
-- Main select filters those for outdated feeds and sorts the stack
-- -- Without the nesting, now()..etc would need to be in all 3 places 'latency' is used instead of just one.
--
-- TODO: Magically turn update_interval 0 into 900 (15 mins)
-- TODO: -- Once that is done, + 30 can go away and * 2 can be used to show missed update cycles
select * from (
        select title, update_interval, now()-(last_updated+(update_interval * INTERVAL '1 minute')) as latency,
                last_updated
        from ttrss_feeds
        where update_interval != -1 AND update_interval < 10000
) s where latency > ( (update_interval + 15 ) * INTERVAL '1 minute') order by latency desc ;