Find a latest gap between Unix timestamps

Question

I currently have two functions that should return the time a device started logging again, the time when the previous row before it was more than 60 seconds away. These functions may work fine but I have to see it work as it takes forever. Is there any shortcuts to make this faster?

CREATE OR REPLACE FUNCTION findNextTime(startt integer)
    RETURNS integer AS
$nextTime$
DECLARE
    nextTime integer;
BEGIN
    select time into nextTime from m01 where time < startt ORDER BY time DESC LIMIT 1;
    return nextTime;
END;
$nextTime$ LANGUAGE plpgsql;

CREATE OR REPlACE FUNCTION findStart()
    RETURNS integer AS
$lastTime$
DECLARE
    currentTime integer;
    lastTime integer;
BEGIN
    select time into currentTime from m01 ORDER BY time DESC LIMIT 1;
    LOOP
        RAISE NOTICE 'Current Time: %', currentTime;
        select findNextTime(currentTime) into lastTime;
        EXIT WHEN ((currentTime - lastTime) > 60);
        currentTime := lastTime;
    END LOOP;
    return lastTime;
END;
$lastTime$ LANGUAGE plpgsql;

To clarify, I want to essentially find the last time there was a break of more than 60 seconds between any two rows.

CREATE TABLE IF NOT EXISTS m01 (
   time integer,
   value decimal,
   id smallint,
   driveId smallint
)

Sample Data:
In this case it would return 1520376063 because the next entry (1520375766) is more than 60 seconds apart it.

| time       | value              | id   | driveid |
|------------|--------------------|------|---------|
| 1520376178 | 516.2              | 5116 | 2       |
| 1520376173 | 507.8              | 5116 | 2       |
| 1520376168 | 499.5              | 5116 | 2       |
| 1520376163 | 491.1              | 5116 | 2       |
| 1520376158 | 482.90000000000003 | 5116 | 2       |
| 1520376153 | 474.5              | 5116 | 2       |
| 1520376148 | 466.20000000000005 | 5116 | 2       |
| 1520376143 | 457.8              | 5116 | 2       |
| 1520376138 | 449.5              | 5116 | 2       |
| 1520376133 | 441.20000000000005 | 5116 | 2       |
| 1520376128 | 432.90000000000003 | 5116 | 2       |
| 1520376123 | 424.6              | 5116 | 2       |
| 1520376118 | 416.20000000000005 | 5116 | 2       |
| 1520376113 | 407.8              | 5116 | 2       |
| 1520376108 | 399.5              | 5116 | 2       |
| 1520376103 | 391.20000000000005 | 5116 | 2       |
| 1520376098 | 382.90000000000003 | 5116 | 2       |
| 1520376093 | 374.5              | 5116 | 2       |
| 1520376088 | 366.20000000000005 | 5116 | 2       |
| 1520376083 | 357.8              | 5116 | 2       |
| 1520376078 | 349.5              | 5116 | 2       |
| 1520376073 | 341.20000000000005 | 5116 | 2       |
| 1520376068 | 332.90000000000003 | 5116 | 2       |
| 1520376063 | 324.5              | 5116 | 2       |
| 1520375766 | 102.5              | 5116 | 2       |

I would suggest that you delete this question. Ask another question and provide sample data and desired results. — Gordon Linoff, Mar 06 '18 at 22:05
`the time when the previous row before it was less than 60 seconds` But your code returns `lastTime` when the previous row is *more* than 60 seconds away. Please clarify. Show the basic table definition (`CREATE TABLE` statement), existing indexes and your version of Postgres. And the `NOTICE` is just for debugging I assume? Apart from that I think I understand the question. — Erwin Brandstetter, Mar 06 '18 at 22:15

Erwin Brandstetter · Accepted Answer · 2018-03-06T23:38:18.587

This simple query should replace your two functions. Note the window function lead() in the subquery:

SELECT *
FROM  (
   SELECT time, lead(time) OVER (ORDER BY time DESC) AS last_time
   FROM   m01
   WHERE  time < _startt
   ) sub
WHERE  time > last_time + 60
ORDER  BY time DESC
LIMIT  1;

Either way, the crucial part for performance is the right index. Ideally on (time DESC).

Assuming time is defined NOT NULL - which it probably should be, but the table definition in the question does not say so. Else you probably want ORDER BY time DESC NULLS LAST - and a matching index. See:

PostgreSQL sort by datetime asc, null first?

I expect this plpgsql function to perform faster, though, if gaps typically show up early:

CREATE OR REPLACE FUNCTION find_gap_before_time(_startt int)
  RETURNS int AS
$func$
DECLARE
   _current_time int;
   _last_time    int;
BEGIN
   FOR _last_time IN  -- single loop is enough!
      SELECT time
      FROM   m01
      WHERE  time < _startt
      ORDER  BY time DESC  -- NULLS LAST?
   LOOP
      IF _current_time > _last_time + 60 THEN  -- never true for 1st row
         RETURN _current_time;
      END IF;
      _current_time := _last_time;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT find_gap_before_time(1520376200);

Result as requested.

Aside: You'd typically save a couple of bytes per row in storage by placing the column value last or first, thereby minimizing alignment padding. Like:

CREATE TABLE m01 (
   time integer,
   id smallint,
   driveId smallint,
   value decimal
);

Detailed explanation:

Calculating and saving space in PostgreSQL

Both work as expected (Thanks!) Original takes about ~14s, function ~6s — dotconnor, Mar 06 '18 at 23:35
@dotoconnor: Nice. (Be sure to have that index.) Also note the added aside. — Erwin Brandstetter, Mar 06 '18 at 23:39
Creating an index on the time col greatly improved the timing, from 6s down to about 100ms, and also improved normal queries so thanks for the tip. — dotconnor, Mar 07 '18 at 01:50

Find a latest gap between Unix timestamps

1 Answers1