Сумма интервалов времени

[WBS]

Есть таблица с событиями (events) с полями:
id_event, time_start, time_end

Допустим, она содержит такие данные:
1, 12:00:00, 17:00:00
2, 13:00:00, 14:00:00
3, 16:30:00, 17:30:00
4, 19:00:00, 20:30:00


Требуется получить:

1.
Общую продолжительность времени, когда происходили какие-то события.
В примере это время с 12:00 до 17:30 (5.5 часов) + время с 19:00 до 20:30 (1.5 часа) = 7 часов.

2.
Сумма времени с группировкой по часам дня.
В примере:
13-й час (с 12:00 до 13:00) - 1 час
14-й час - 2 часа
15-й час - 1 час
16-й час - 1 час
17-й час - 1.5 часа
18-й час - 0.5 часа
19-й час - 0 часов
20-й час - 1 час
21-й час - 0.5 часа


Подскажите, можно ли каждую из двух задач решить одним запросом к БД и как это сделать?
Интересует общий принцип составления таких запросов. Использовать планирую в MySQL и PostgreSQL.

 

[Gas]

ну а как пытался то сделать? тут же простые операции - вычитание, суммирование и группировка.

 

[WBS]

ну а как пытался то сделать? тут же простые операции

Считаю, что обе задачи сложные. Пока даже не представляю, как к ним подступиться.
Есть идея, как решить первую задачу программно. Но хочу все сделать запросами.

 

[С.]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

 

[WBS]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Мануал я в состоянии прочитать и самостоятельно. За стандартными простыми решениями на форум я не обращаюсь. Чем мне поможет Ваша ссылка?

Вы суть задач вообще поняли?
Какими стандартными функциями можно из исходной таблицы получить ответ "7 часов" (т.е. 25200 секунд)? (это первая задача)
Для простоты можно условиться, что в БД хранятся простые числа timestamp. Что дальше мне с этими числами делать? Вот в чем вопрос.

 

[С.]

Похоже не в состянии прочитать. Читаю за тебя: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_timediff
Естественно "7 часов" в результате она не даст, но совсем уже тупить не стоит наверное.

 

[Тугай]

С.
Интервалы по условию пресекаются. Перед тем как считать нужно объединить пересекающиеся.
Так сходу и не уверен можно ли это одним запросом.

Решение влоб создать временную таблицу, в нее объеденить пересекающиеся интервалы, потом уже задача тривиальная.

Навскидку одним запросом (time_start - a; time_and - b):

select sum(end-start)

from  (

select distinct min( if ( t2.a is null, t1.a, t2.a) ) as start, max( if ( t3.b is null, t1.b, t3.b) ) as end
from  t1 left outer join t2 on ( t2.a between t1.a and t1.b)
left outer join t3 on (t3.a between t1.a and t1.b)

) where start>='12:00' and end <= '17:30'

 

[WBS]

Естественно "7 часов" в результате она не даст, но совсем уже тупить не стоит наверное.

Поддерживаю! Разберитесь для начала с условиями задачи.
В PostgreSQL, кстати, допускается запись без лишних функций:

SELECT SUM(time_end - time_start) FROM events

Как Вы верно подметили, "7 часов" это не даст.

 

[WBS]

Решение создать временную таблицу, в нее объеденить пересекающиеся интервалы, потом уже задача тривиальная.

Решение не идеальное, но приемлемое, если во временной таблице можно будет объединить интервалы одним запросом. Но как это сделать?

Более сложные программные решения (с циклами или рекурсией) я держу про запас, но использовать их не хотелось бы.

 

[Sad Spirit]

Первая задача, одним запросом:

select sum(time_end - time_start) - (
   select sum(least(second.time_end, first.time_end) - greatest(second.time_start, first.time_start)) as common
   from events as first, events as second
   where first.id_event < second.id_event and
         (first.time_start, first.time_end) overlaps (second.time_start, second.time_end)
)
from events;

Вторую ща накидаю, там с generate_series() надо...

 

[Sad Spirit]

Вторая задача:

select c + 1, sum(least(hour_end, time_end) - greatest(time_start, hour_start))
from events, 
    (
        select c, cast(c || ':00:00' as time) as hour_start, cast((c + 1) || ':00:00' as time) as hour_end
        from generate_series(0, 23) as c
    ) as hours
where (time_start, time_end) overlaps (hour_start, hour_end)
group by c
order by c;

 

[Sad Spirit]

Есессно, тут мы закладываемся на то, что time_end всегда больше time_start, то есть события у нас через нуль часов нуль минут не перескакивают.

 

[fixxxer]

Первая задача, одним запросом:

select sum(time_end - time_start) - (
   select sum(least(second.time_end, first.time_end) - greatest(second.time_start, first.time_start)) as common
   from events as first, events as second
   where first.id_event < second.id_event and
         (first.time_start, first.time_end) overlaps (second.time_start, second.time_end)
)
from events;

Вторую ща накидаю, там с generate_series() надо...

ммммм
подозреваю, что будет работать неправильно на входных данных вида
12:00:00 - 13:00:00
13:00:00 - 13:30:00

 

[Sad Spirit]

ммммм
подозреваю, что будет работать неправильно на входных данных вида
12:00:00 - 13:00:00
13:00:00 - 13:30:00

Пролетарская чуйка? Или протестировал?

 

[fixxxer]

Сначала первое, потом второе =) В общем действительно не работает. Надо подумать как это пофиксить малыми силами, в голову пока приходят только хаки вида "добавить секунду"

 

[Sad Spirit]

Сначала первое, потом второе =) В общем действительно не работает. Надо подумать как это пофиксить малыми силами, в голову пока приходят только хаки вида "добавить секунду"

Не понял насичот не работает:

postgres=# select ('12:00:00'::time, '13:00:00'::time) overlaps ('13:00:00'::time, '13:30:00'::time);
 overlaps
----------
 f
(1 строка)

как собственно и сказано в документации:

This means for instance that two time periods with only an endpoint in common do not overlap.

 

[WBS]

where start>='12:00' and end <= '17:30'

Настораживают эти ограничения времени в условиях. Но все равно спасибо за участие.

Первая задача, одним запросом

Очень интересное решение, спасибо.

Но по-моему оно неправильно отработает таблицу:
1, 15:00:00, 16:00:00
2, 15:00:00, 16:00:00
3, 15:00:00, 16:00:00
И любую другую, в которой интервалы пересекаются более 1-го раза.

Сначала мы получим сумму времени (3 часа), а затем вычтем пересечения
- id1 vs id2 (-1 час)
- id1 vs id3 (-1 час)
- id2 vs id3 (-1 час)
Останется 0 часов, а нужно 1 час.

По второй задаче мне еще нужно время, чтобы разобраться

 

[fixxxer]

Не понял насичот не работает:

postgres=# select ('12:00:00'::time, '13:00:00'::time) overlaps ('13:00:00'::time, '13:30:00'::time);
 overlaps
----------
 f
(1 строка)

как собственно и сказано в документации:

postgres=# select * from t;
 id | time_start | time_end
----+------------+----------
  1 | 12:00:00   | 13:00:00
  4 | 13:00:00   | 13:31:00
(2 rows)

postgres=# select sum(time_end - time_start) - (
postgres(#    select sum(least(second.time_end, first.time_end) - greatest(second.time_start, first.time_start)) as common
postgres(#    from t as first, t as second
postgres(#    where first.id < second.id and
postgres(#          (first.time_start, first.time_end) overlaps (second.time_start, second.time_end)
postgres(# )
postgres-# from t;
 ?column?
----------

(1 row)

 

[Тугай]

Sad Spirit:
Данные:
1 - 5
3 - 7
4 - 9

sum(time_end - time_start) = 4 + 4 + 5 = 13

sum(least(second.time_end, first.time_end) - greatest(second.time_start, first.time_start)):
5 - 3 + 5 -4 = 3
для второй строчки:
7 -4 = 3
для 3й понятно: 0
Ну и 13-3-3 = 7 != 9-1=8

Неправильно похоже. У меня тоже не правильно.

 

[Sad Spirit]

fixxer, спасибо, проблему понял.

С добавлением coalesce() всё работает как нада:

select sum(time_end - time_start) - coalesce((
   select sum(least(second.time_end, first.time_end) - greatest(second.time_start, first.time_start)) as common
   from events as first, events as second
   where first.id_event < second.id_event and
         (first.time_start, first.time_end) overlaps (second.time_start, second.time_end)
), '0'::interval)
from events;

выплёвывает

 ?column?
----------
 01:31:00
(1 строка)

WBS, да, с несколькими пересечениями некоторая засада получилась... подумаю, как правильно разрулить.