title: Find a latest gap between Unix timestamps
body: <p>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?</p>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION findNextTime(startt integer)
      RETURNS integer AS
  $nextTime$
  DECLARE
      nextTime integer;
  BEGIN
      select time into nextTime from m01 where time &lt; 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) &gt; 60);
          currentTime := lastTime;
      END LOOP;
      return lastTime;
  END;
  $lastTime$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>To clarify, I want to essentially find the last time there was a break of more than 60 seconds between any two rows.</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE IF NOT EXISTS m01 (
     time integer,
     value decimal,
     id smallint,
     driveId smallint
  )
  </code></pre>
  
  <p>Sample Data:<br>
  In this case it would return 1520376063 because the next entry (1520375766) is more than 60 seconds apart it.</p>
  
  <pre class="lang-sql prettyprint-override"><code>| 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       |
  </code></pre>