Postgres group records by consecutive types I would like to group objects by their consecutive start and end date.
+----------+----------------------------+
|  Fruit   | Time                       |
+----------+----------------------------+
| Apple    | 2020-09-08 00:00:00.000000 | 
| Apple    | 2020-09-08 01:00:00.000000 | 
| Orange   | 2020-09-08 02:00:00.000000 | 
| Orange   | 2020-09-08 03:00:00.000000 | 
| Apple    | 2020-09-08 04:00:00.000000 | 
+----------+---------------+------------+

The results should look like this:
+----------+----------------------------+----------------------------+
|  Fruit   | Start Time                 | End Time                   |
+----------+----------------------------+----------------------------+
| Apple    | 2020-09-08 00:00:00.000000 | 2020-09-08 01:00:00.000000 |
| Orange   | 2020-09-08 02:00:00.000000 | 2020-09-08 03:00:00.000000 |
| Apple    | 2020-09-08 04:00:00.000000 | 2020-09-08 04:00:00.000000 |
+----------+----------------------------+----------------------------+
This is a gaps-and-islands problem, and in this specific case you can solve it using a method that involves two ROW_NUMBER calls to identify distinct &quot;islands&quot; of consecutive rows belonging to the same Fruit:
WITH
  partitioned AS
  (
    SELECT
      Fruit,
      FruitTime,
      ROW_NUMBER() OVER (                   ORDER BY FruitTime ASC) -
      ROW_NUMBER() OVER (PARTITION BY Fruit ORDER BY FruitTime ASC) AS IslandID
    FROM
      YourTable
  )
SELECT
  Fruit,
  MIN(FruitTime) AS StartTime,
  MAX(FruitTime) AS EnfTime
FROM
  partitioned
GROUP BY
  Fruit,
  IslandID
ORDER BY
  StartTime ASC
;

Demo at dbfiddle.uk: https://dbfiddle.uk/?rdbms=postgres_12&amp;fiddle=f7402a206a9f12afec40ce242b2bca92