Вычисление интервалов времени SQL : MySQL & MariaDB

EugeneT · █ 04.08.2017 12:22

Не нашел раздела по firebird, но думаю вопрос скорее обще-SQL, чем относящийся к какому-то диалекту.

Имеем примерно такую таблицу:

Код:


ФИО        Дата            Время        Направление
----------------------------------------------
Иванов 31.07.2017     08:30:00    Вход
Иванов 31.07.2017     10:30:00    Выход
Иванов 31.07.2017     12:30:00    Вход
Иванов 31.07.2017     17:30:00    Выход
....

Ну разными несложными штуками удалось разложить вход и выход в разный колонки, потом через min и max показать время прихода и время ухода, без промежуточных выбегов на обед и покурить. Так вот теперь очень хочется посчитать время которое человек находился внутри, то бишь (Tвых1-Tвх1)+(Твых2-Твх2)+....+(ТвыхN-ТвхN)
Можно такое соорудить на sql или вытащить куда-нить в php?

Micle · █ 04.08.2017 13:29

так посщитай время каждого из прибываний и сложи

vdm · █ 04.08.2017 14:09

Если уже есть интервалы, то вопрос только в поиске подходящих функций работы с датами для своего диалекта sql.

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

Тут нумерация интервалов - через переменные mysql, в других sql аналогичные функции подобрать можно.

SQL код:

 SELECT t2.ФИО,
       MIN(t2.ДатаВремя) Вход,
       MAX(t2.ДатаВремя) Выход,
       (UNIX_TIMESTAMP(MAX(t2.ДатаВремя)) - UNIX_TIMESTAMP(MIN(t2.ДатаВремя))) ВремяВнутри
FROM (
SELECT t.ФИО, 
       STR_TO_DATE(CONCAT_WS(' ',t.Дата,t.Время),'%d.%m.%Y %H:%i:%s') ДатаВремя,
       t.Направление,
       IF (t.Направление='Вход', @group:=@group+1,@group) Период
FROM (
    SELECT 'Иванов' ФИО, '31.07.2017' Дата, '08:30:00' Время, 'Вход' Направление UNION ALL
    SELECT 'Иванов', '31.07.2017', '10:30:00', 'Выход' UNION ALL
    SELECT 'Иванов', '31.07.2017', '12:30:00', 'Вход' UNION ALL
    SELECT 'Иванов', '31.07.2017', '17:30:00', 'Выход'
) t, (SELECT @group:=0) g
ORDER BY t.ФИО,Период,ДатаВремя
) t2
GROUP BY t2.ФИО, t2.Период
;

ВремяВнутри в секундах - просуммировать и перевести в дни:часы:минуты.