Get row with the smallest time interval per value in a table

Question

Here is my table:

Start Time            Stop time                 extension
----------------------------------------------------------
2014-03-03 10:00:00   2014-03-03 11:00:00         100
2014-03-03 10:00:00   2014-03-03 12:00:00         100
2014-03-05 10:00:00   2014-03-05 11:00:00         200
2014-03-03 10:00:00   2014-03-03 13:00:00         100
2014-03-05 10:00:00   2014-03-05 12:00:00         200
2014-03-05 10:00:00   2014-03-05 13:00:00         200

I want to get the smallest time interval for each extension:

Start Time            Stop time                  Extension
-------------------------------------------------------------
2014-03-03 10:00:00   2014-03-03 11:00:00         100
2014-03-05 10:00:00   2014-03-05 11:00:00         200

How can I write the sql?

Get the single smallest time interval in the table or the smallest time interval for each start or something? — Erwin Brandstetter, May 17 '14 at 13:58
Now all we need is Craig Ringer to pitch in and @gameip has all the PostgreSQL high-hitters assembled in one post before even hitting 10 rep. Surely there must be a gold badge for that??? -[;-D — Patrick, May 17 '14 at 14:15
The question is unclear: you need the smallest interval, or the row with the min interval? (Or something else?) — Denis de Bernardy, May 17 '14 at 14:57
I have added more details about the questions. In fact, it's not only get one record. I want to get the result according to extension. Thanks! — gameip, May 17 '14 at 18:38

score 1 · Answer 1 · answered May 17 '14 at 13:47

not sure what exactly you are after, but the "smallest interval" would be

select min(stop_time - start_time) 
from the_table

If you also need the two columns with that:

select start_time, stop_time, duration
from (
   select start_time, 
          stop_time,
          stop_time - start_time as duration,
          min(stop_time - start_time) as min_duration
   from the_table
) t
where duration = min_duration;

The above would yield more than one row if multiple rows have the same duration. If you don't want that you can use:

select start_time, stop_time, duration
from (
   select start_time, 
          stop_time,
          stop_time - start_time as duration,
          row_numer() over (order by stop_time - start_time) as rn
   from the_table
) t
where rn = 1;

wildplasser · Answer 2 · 2014-05-18T00:03:24.343

CREATE TABLE fluff
        ( id SERIAL NOT NULL PRIMARY KEY
        , starttime TIMESTAMP NOT NULL
        , stoptime TIMESTAMP NOT NULL
        );

INSERT INTO fluff(starttime,stoptime) VALUES
  ('2014-03-03 10:00:00' , '2014-03-03 11:00:00' )
, ('2014-03-03 10:00:00' , '2014-03-03 12:00:00' )
, ('2014-03-03 10:00:00' , '2014-03-03 13:00:00' )
        ;

SELECT * FROM fluff fl
WHERE NOT EXISTS (
        SELECT *
        FROM fluff nx
        WHERE AGE(nx.stoptime,nx.starttime) < AGE(fl.stoptime,fl.starttime)
        );

Okay, After the update:

CREATE TABLE fluff2
        ( id SERIAL NOT NULL PRIMARY KEY
        , starttime TIMESTAMP NOT NULL
        , stoptime TIMESTAMP NOT NULL
        , bagger INTEGER NOT NULL
        );
;

INSERT INTO fluff2(starttime,stoptime, bagger) VALUES
  ( '2014-03-03 10:00:00', '2014-03-03 11:00:00',100)
, ( '2014-03-03 10:00:00', '2014-03-03 12:00:00',100)
, ( '2014-03-05 10:00:00', '2014-03-05 11:00:00',200)
, ( '2014-03-03 10:00:00', '2014-03-03 13:00:00',100)
, ( '2014-03-05 10:00:00', '2014-03-05 12:00:00',200)
, ( '2014-03-05 10:00:00', '2014-03-05 13:00:00',200)
        ;

SELECT * FROM fluff2 fl
WHERE NOT EXISTS (
        SELECT *
        FROM fluff2 nx
        WHERE nx.bagger = fl.bagger
        AND AGE(nx.stoptime,nx.starttime) < AGE(fl.stoptime,fl.starttime)
        );

score 0 · Answer 3 · answered May 17 '14 at 14:03

0

I would use order by and limit:

select t.*
from table t
order by stop_time - start_time asc
limit 1;

This gives you all the columns in the table for the smallest duration.

answered May 17 '14 at 14:03

Gordon Linoff

1,242,037
58
646
786

score 0 · Accepted Answer · edited May 23 '17 at 10:32

To get the row (including all original columns) with the smallest time interval for each extension (according to your updated question) the Postgres specific DISTINCT ON should be most convenient:

SELECT DISTINCT ON (extension)
       start_time, stop_time, extension
FROM   tbl
ORDER  BY extension, (stop_time - start_time);

Details in this related answer:
Select first row in each GROUP BY group?

Get row with the smallest time interval per value in a table

4 Answers4