Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Помогите сделать выборку по времени

Автор: AonMaster 7.10.2014, 14:37

Есть лог файл, такого вида:
2014-10-04 01:16:17 - Открыта дверь подъезда
2014-10-04 01:16:26 - Открыта дверь подъезда
2014-10-04 03:01:14 - Открыта дверь подъезда
2014-10-04 03:01:24 - Открыта дверь подъезда
2014-10-04 04:43:13 - Открыта дверь подъезда
2014-10-04 04:43:23 - Открыта дверь подъезда
2014-10-04 06:44:14 - Открыта дверь подъезда
2014-10-04 06:44:23 - Открыта дверь подъезда
2014-10-04 06:44:38 - Открыта дверь подъезда

Предполагается запихать его в базу MySQL.
Как видите, записи идут с интервалом около 10 секунд (должно определяться в запросе, скажем, N) - первая запись начало события, последняя - конец. Иногда записей внутри интервала N секунд бывает больше (последние 3 строки - 3 записи, но считаем, что событие одно).

Помогите написать запрос, чтоб результат выглядел так:
01:16:17 - 01:16:26 открыта дверь
03:01:14 - 03:01:24 открыта дверь
04:43:13 - 04:43:23 открыта дверь
06:44:14 - 06:44:38 открыта дверь

Если можно, с переходом через 00 часов (думаю, если в базе хранить дату в timestamp - это получится и так).
в инете находил лишь примеры группировки по строго заданным интервалам, скажем, с 00:10 до 00:20, а тут другое.

Вот моя таблица:

mysql> select * from domofon;

Код


+----+---------------------+-------+
| id | date                | event |
+----+---------------------+-------+
|  1 | 2014-10-04 01:16:17 | 1     |
|  2 | 2014-10-04 01:16:26 | 1     |
|  3 | 2014-10-04 03:01:14 | 1     |
|  4 | 2014-10-04 03:01:24 | 1     |
+----+---------------------+-------+

4 rows in set (0.01 sec)

Заранее спасибо!

Автор: tzirechnoy 8.10.2014, 16:22

Код

GROUP BY round(EXTRACT( EPOCH from date )/N);

Это для postgres, но идея думаю понятна.

Автор: AonMaster 8.10.2014, 20:02

Увы, не понятна :(

в mysql такого нет?

Автор: Zloxa 8.10.2014, 20:57

select min(date), max(date)
from domofon
group by http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_truncate(http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp/N)

Автор: AonMaster 12.10.2014, 05:30

Zloxa,
Спасибо) в первом приближении работает, но не всегда, т.к. высчитывает не разницу в секундах, а по-другому. Иногда округляет не туда.

Поточнее есть вариант?

Автор: Zloxa 12.10.2014, 11:14

Цитата(AonMaster @ 12.10.2014, 06:30

)

Поточнее есть вариант?

поточнее можете свормулирввать, чем не устраивает предложенное?

Автор: AonMaster 13.10.2014, 14:00

Не всегда правильно разбивает по интервалам в 10 или 13 секунд.

Автор: Akina 13.10.2014, 14:33

Zloxa, По-моему, ему надо вот что: выбросить из выборки все записи, для которых разность между этой и следующей при сортировке по времени записи менее установленного интервала (10 секунд, например). В принципе, получается, что-то типа

Код



SELECT t1.*
FROM table t1
LEFT JOIN table t2
ON DATEDIFF('s',t1.datetime, t2.datetime) BETWEEN 0 AND 10
WHERE t2.datetime IS NULL