Как выбрать крайние значения времени до скачка/разрыва из MySQL?

Question

[shakawkaw]

Помогите выбрать граничные значения типа TIMESTAMP в MySQL. Есть таблица со штампами времени. Штампы идут пачками с интервалом 1 секунду и случайными промежутками между пачками. Есть ли способ на чистом sql получить начальное и конечное значение каждой пачки?
например, значения:
1442800001
1442800002
1442800003
1442800004
1442866661
1442866662
1442866663

получить как:
+----------------+-----------------+
|..........t1.........|...........t2.........|
+----------------+-----------------+
| 1442800001 | 1442800004 |
| 1442866661 | 1442866663 |
+----------------+-----------------+

Answer 1

[Rsa97]

Изврат, конечно:

SET @t = 0;
SET @p = 0;
SELECT MIN(`time`) AS `t1`, MAX(`time`) AS `t2` 
    FROM (
        SELECT `time`, @t AS `prev`, 
                @p := IF(@t+1 = `time`, @p, @p+1) AS `packet`, 
                @t := `time` 
        FROM `table` 
        ORDER BY `time`
    ) AS `t` 
    GROUP BY `packet` 
    ORDER BY `packet`;

Comments

[shakawkaw]

не работает, к сожалению, извиняюсь за большой листинг, но вот так у меня:

mysql> select * from `table`;
+---------------------+
| time |
+---------------------+
| 2015-09-21 04:46:41 |
| 2015-09-21 04:46:42 |
| 2015-09-21 04:46:43 |
| 2015-09-21 04:46:44 |
| 2015-09-21 06:37:41 |
| 2015-09-21 06:37:42 |
| 2015-09-21 06:37:43 |
+---------------------+
7 rows in set (0.00 sec)

mysql> SET @t = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @p = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MIN(`time`) AS `t1`, MAX(`time`) AS `t2`
-> FROM (
-> SELECT `time`, @t AS `prev`,
-> @p := IF(@t+1 = `time`, @p, @p+1) AS `packet`,
-> @t := `time`
-> FROM `table`
-> ORDER BY `time`
-> ) AS `t`
-> GROUP BY `packet`
-> ORDER BY `packet`;
+---------------------+---------------------+
| t1 | t2 |
+---------------------+---------------------+
| 2015-09-21 04:46:41 | 2015-09-21 04:46:41 |
| 2015-09-21 04:46:42 | 2015-09-21 04:46:42 |
| 2015-09-21 04:46:43 | 2015-09-21 04:46:43 |
| 2015-09-21 04:46:44 | 2015-09-21 04:46:44 |
| 2015-09-21 06:37:41 | 2015-09-21 06:37:41 |
| 2015-09-21 06:37:42 | 2015-09-21 06:37:42 |
| 2015-09-21 06:37:43 | 2015-09-21 06:37:43 |
+---------------------+---------------------+

[Rsa97]

shakawkaw: Так вы уж определитесь с типом данных. Конечно здесь условие @t+1 = `time` работать не будет.

SET @t = '2000-01-01 00:00:00';
SET @p = 0;
SELECT MIN(`time`) AS `t1`, MAX(`time`) AS `t2` 
    FROM (
        SELECT `time`, @t AS `prev`,
                @p := IF(@t = (`time`-INTERVAL 1 SECOND), @p, @p+1) AS `packet`, 
                @t := `time` 
        FROM `table` 
        ORDER BY `time`
    ) AS `t` 
    GROUP BY `packet` 
    ORDER BY `packet`;