Ответ 1

Запрос:

Предполагая, что поля, содержащие интервал, называются Start и Finish, а таблица имеет имя YOUR_TABLE, запрос...

SELECT Finish, Start
FROM
    (
        SELECT DISTINCT Start, ROW_NUMBER() OVER (ORDER BY Start) RN
        FROM YOUR_TABLE T1
        WHERE
            NOT EXISTS (
                SELECT *
                FROM YOUR_TABLE T2
                WHERE T1.Start > T2.Start AND T1.Start < T2.Finish
            )
        ) T1
    JOIN (
        SELECT DISTINCT Finish, ROW_NUMBER() OVER (ORDER BY Finish) RN
        FROM YOUR_TABLE T1
        WHERE
            NOT EXISTS (
                SELECT *
                FROM YOUR_TABLE T2
                WHERE T1.Finish > T2.Start AND T1.Finish < T2.Finish
            )
    ) T2
    ON T1.RN - 1 = T2.RN
WHERE
    Finish < Start

... дает следующие результаты в ваших тестовых данных:

Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000

Важным свойством этого запроса является то, что он будет работать с перекрывающимися интервалами.

Алгоритм:

1. Интервалы перекрытия слияния

Подзапрос T1 принимает только те интервалы, которые вне других интервалов. Подзапрос T2 делает то же самое для окончания интервала. Это то, что удаляет перекрытия.

DISTINCT важен в случае, если есть два одинаковых начала (или окончания) интервалов, которые находятся вне других интервалов. WHERE Finish < Start просто исключает любые пустые интервалы (т.е. Продолжительность 0).

Мы также присоединяем номер строки относительно временного порядка, который понадобится на следующем шаге.

T1 дает:

Start                       RN
2012-01-01 08:00:00.000     1
2012-01-01 18:00:00.000     2

T2 дает:

Finish                      RN
2012-01-01 10:00:00.000     1
2012-01-01 20:00:00.000     2

2. Восстановить результат

Теперь мы можем восстановить либо "активные", либо "неактивные" интервалы.

Неактивные интервалы восстанавливаются путем объединения конца предыдущего интервала с началом следующего, следовательно - 1 в предложении ON. Эффективно мы поставили...

Finish                      RN
2012-01-01 10:00:00.000     1

... и...

Start                       RN
2012-01-01 18:00:00.000     2

... вместе, в результате чего:

Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000

(Активные интервалы можно восстановить, поместив строки из T1 рядом с строками из T2, используя JOIN ... ON T1.RN = T2.RN и вернув WHERE.)

Пример:

Вот несколько более реалистичный пример. Следующие тестовые данные:

Device      Event      Start                      Finish
Device 1    Event A    2012-01-01 08:00:00.000    2012-01-01 10:00:00.000
Device 2    Event B    2012-01-01 18:00:00.000    2012-01-01 20:00:00.000
Device 3    Event C    2012-01-02 11:00:00.000    2012-01-02 15:00:00.000
Device 4    Event D    2012-01-02 10:00:00.000    2012-01-02 12:00:00.000
Device 5    Event E    2012-01-02 10:00:00.000    2012-01-02 15:00:00.000
Device 6    Event F    2012-01-03 09:00:00.000    2012-01-03 10:00:00.000

Дает следующий результат:

Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000
2012-01-01 20:00:00.000     2012-01-02 10:00:00.000
2012-01-02 15:00:00.000     2012-01-03 09:00:00.000

Ответ 2

Первый ответ - но см. ниже для окончательного с дополнительными ограничениями, добавленными OP.

- Если вы хотите получить следующий startTime после самого последнего endTime и избежать совпадений, вам нужно что-то вроде:

select
    distinct
    e1.deviceId,
    e1.EventEnd,
    e3.EventStart
from Events e1 
join Events e3 on e1.eventEnd < e3.eventStart     /* Finds the next start Time */
and e3.eventStart = (select min(eventStart) from Events e5
                     where e5.eventStart > e1.eventEnd)
and not exists (select *                          /* Eliminates an e1 rows if it is overlapped */
                from Events e5 
                where e5.eventStart < e1.eventEnd
                    and e5.eventEnd > e1.eventEnd)

В случае трех ваших строк:

INSERT INTO Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')
insert into Events values (2, '01/01/2012 09:00', '01/01/2012 11:00')

Это дает 1 результат:

January, 01 2012 11:00:00-0800  January, 01 2012 18:00:00-0800

Однако, я предполагаю, что вы, вероятно, захотите также найти на DeviceId. В этом случае в объединениях вы добавляете e1.DeviceId = e3.DeviceId и e1.deviceId = e5.deviceId

SQL Fiddle здесь: http://sqlfiddle.com/#!3/3899c/8

-

ОК, окончательное редактирование. Здесь запрос, добавляющий в deviceIds и добавляющий в отдельную учетную запись для одновременного завершения событий:

SELECT distinct
    e1.DeviceID,
    e1.EventEnd as LastEndTime,
    e3.EventStart as NextStartTime
FROM Events e1 
join Events e3 on e1.eventEnd < e3.eventStart
     and e3.deviceId = e1.deviceId
     and e3.eventStart = (select min(eventStart) from Events e5
                     where e5.eventStart > e1.eventEnd
                    and e5.deviceId = e3.deviceId)
where not exists (select * from Events e7 
                    where e7.eventStart < e1.eventEnd
                      and e7.eventEnd > e1.eventEnd
                      and e7.deviceId = e1.deviceId)
order by e1.deviceId, e1.eventEnd

Присоединение к e3 находит следующий старт. Присоединение к e5 гарантирует, что это самое раннее время запуска после текущего времени окончания. Соединение с e7 исключает строку, если конец рассматриваемой строки перекрывается другой строкой.

Для этих данных:

INSERT INTO Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')
insert into Events values (2, '01/01/2012 09:00', '01/01/2012 11:00')
insert into Events values (2, '01/02/2012 11:00', '01/02/2012 15:00')
insert into Events values (1, '01/02/2012 10:00', '01/02/2012 12:00')
insert into Events values (2, '01/02/2012 10:00', '01/02/2012 15:00')
insert into Events values (2, '01/03/2012 09:00', '01/03/2012 10:00')

Вы получите этот результат:

1   January, 01 2012 10:00:00-0800  January, 02 2012 10:00:00-0800
2   January, 01 2012 11:00:00-0800  January, 01 2012 18:00:00-0800
2   January, 01 2012 20:00:00-0800  January, 02 2012 10:00:00-0800
2   January, 02 2012 15:00:00-0800  January, 03 2012 09:00:00-0800

SQL Fiddle здесь: http://sqlfiddle.com/#!3/db0fa/3

Ответ 3

Помогает ли это решить вашу проблему:

• http://www.simple-talk.com/sql/t-sql-programming/find-missing-date-ranges-in-sql/
• http://www.simple-talk.com/sql/t-sql-programming/missing-date-ranges--the-sequel/

Вторая кажется более актуальной

'Существует таблица, где два столбца - DateFrom и DateTo. Оба столбца содержат значения даты и времени. Как найти отсутствующие диапазоны дат или, другими словами, все диапазоны дат, которые не охватываемых ни одной из записей в таблице ".

Ответ 4

Я собираюсь предположить, что это не совсем так просто... но вот запрос, основанный на моем нынешнем понимании вашего сценария:

DECLARE @Events TABLE (
    DeviceID INT,
    EventStart DATETIME,
    EventEnd DATETIME
)

INSERT INTO @Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO @Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')

SELECT
    e1.DeviceID,
    e1.EventEnd,
    e2.EventStart
FROM 
    @Events e1 
    JOIN @Events e2 
        ON e2.EventStart = (
            SELECT MIN(EventStart)
            FROM @Events
            WHERE EventStart > e1.EventEnd
        )

Ответ 5

Я не очень понимаю ваш вопрос:

"У меня есть таблица с идентификатором устройства, EventStart и EventEnd times"

за которым следует

"Мне нужно запустить запрос, чтобы получить время между этими событиями"

"Например:

Device 1 Event A runs from 01/01/2012 08:00 - 01/01/2012 10:00 "

Для этого вам нужно всего лишь сделать

select Device_ID,EventStart,EventEnd from Device 

Если вы хотите получить разницу во времени между датами начала и окончания для каждого устройства, вы можете использовать DATEDIFF:

select Device_ID,DATEDIFF(EventEnd - EventStart) from Device

Ответ 6

Вот решение Postgres, которое я только что сделал, что не содержит хранимых процедур:

SELECT minute, sum(case when dp.id is null then 0 else 1 end) as s 
FROM generate_series( 
   '2017-12-28'::timestamp,
   '2017-12-30'::timestamp,
   '1 minute'::interval
) minute 
left outer join device_periods as dp
on minute >= dp.start_date and minute < dp.end_date 
group by minute order by minute

Функция generate_series генерирует таблицу, которая имеет одну строку для каждой минуты в диапазоне дат. Вы можете изменить интервал на 1 секунду, если быть более точным. Это специальная функция postgres, но, вероятно, что-то подобное существует в других машинах.

Этот запрос предоставит вам все заполненные минуты и все, что пусто. Вы можете обернуть этот запрос во внешний запрос, который может группироваться по часам, дням или выполнять некоторые функции оконных функций, чтобы получить точный результат по мере необходимости. Для моих целей мне нужно было только подсчитать, есть ли пробелы или нет.