Geek asks and answers

How to find the appropriate time intervals for more than 2 users

Find the best suitable time from a given time interval for different users.

Rows: 5
fid  userid  FromDateTime           ToDateTime          flag
62   1   2012-07-18 01:48:20    2012-07-18 02:55:20     1
63   1   2012-07-18 10:30:46    2012-07-18 12:54:46     1
64   1   2012-07-18 18:50:24    2012-07-18 20:35:24     1
67   1   2012-07-18 15:03:36    2012-07-18 16:03:36     1
68   2   2012-07-18 21:10:47    2012-07-18 23:10:47     1

Above the table shows the various free time periods available for different users, for reference:

user1 free in

2012-07-18 01:48:20   to   2012-07-18 02:55:20 , 
2012-07-18 10:30:46   to   2012-07-18 12:54:46 
......

user 2 free only between this period of time:

2012-07-18 21:10:47   to   2012-07-18 23:10:47

Now I want to find out one best time interval in which both users can schedule a meeting.

+5
source share
5 answers

To find out if user user1 and user2 are free, follow these steps:

select 
a.datetime_start as user1start,a.datetime_end as user1end,
b.datetime_start as user2start,b.datetime_end as user2end ,
case when a.datetime_start > b.datetime_start then a.datetime_start 
   else b.datetime_start end as avail_start,
case when a.datetime_end>b.datetime_end then b.datetime_end 
   else a.datetime_end end as avail_end
from users a inner join users b on
a.datetime_start<=b.datetime_end and a.datetime_end>=b.datetime_start     
and  a.userid={user1} and b.userid={user2}

SQL FIDDLE HERE.

Edition: To compare more than 2 users, try the following:

select max(datetime_start) as avail_start,min(datetime_end) as avail_end
from(
        select *,
        @rn := CASE WHEN @prev_start <=datetime_end and @prev_end >=datetime_start THEN @rn ELSE @rn+1 END AS rn,
        @prev_start := datetime_start,
        @prev_end := datetime_end 
        from(
          select * from users2 m
          where exists ( select null 
                          from users2 o 
                           where o.datetime_start <= m.datetime_end and o.datetime_end >= m.datetime_start
                           and o.id <> m.id 
                        ) 
             and m.userid in (2,4,3,5)
           order by m.datetime_start) t,
           (SELECT @prev_start := -1, @rn := 1, @prev_end=-1) AS vars 
) c 
group by rn 
having count(rn)=4 ;

m.userid in (2,4,3,5) having count(rn)=4 .

SQL FIDDLE

+8

, "" , ( userids 1-5). "" .

SELECT   MAX(b.FromDateTime) FromDateTime, 
         a.ToDateTime
FROM     (
         SELECT   DISTINCT a.ToDateTime
         FROM     tbl a
         JOIN     tbl b ON a.userid     <> b.userid
                       AND a.userid     IN (1,2,3,4,5)
                       AND b.userid     IN (1,2,3,4,5)
                       AND a.ToDateTime >  b.FromDateTime 
                       AND a.ToDateTime <= b.ToDateTime
         GROUP BY a.userid,
                  a.FromDateTime,
                  a.ToDateTime
         HAVING   COUNT(DISTINCT b.userid) = 4
         ) a
JOIN     (
         SELECT   DISTINCT a.FromDateTime
         FROM     tbl a
         JOIN     tbl b ON a.userid       <> b.userid
                       AND a.userid       IN (1,2,3,4,5)
                       AND b.userid       IN (1,2,3,4,5)
                       AND a.FromDateTime >= b.FromDateTime 
                       AND a.FromDateTime <  b.ToDateTime
         GROUP BY a.userid,
                  a.FromDateTime,
                  a.ToDateTime
         HAVING   COUNT(DISTINCT b.userid) = 4
         ) b ON b.FromDateTime < a.ToDateTime
GROUP BY a.ToDateTime
ORDER BY TIMESTAMPDIFF(SECOND, MAX(b.FromDateTime), a.ToDateTime) DESC
LIMIT    1

4 COUNT(DISTINCT... - ( ). .

- , .

:

(62, 1, '2012-07-18 00:00:00', '2012-07-18 12:00:00', 1),

(63, 2, '2012-07-18 00:00:00', '2012-07-18 02:00:00', 1),
(64, 2, '2012-07-18 03:00:00', '2012-07-18 05:00:00', 1),
(65, 2, '2012-07-18 05:30:00', '2012-07-18 06:00:00', 1),

(66, 3, '2012-07-18 00:30:00', '2012-07-18 02:30:00', 1),
(67, 3, '2012-07-18 03:10:00', '2012-07-18 07:30:00', 1),

(68, 4, '2012-07-18 01:10:00', '2012-07-18 03:20:00', 1),
(69, 4, '2012-07-18 03:50:00', '2012-07-18 06:00:00', 1),

(70, 5, '2012-07-18 01:10:00', '2012-07-18 03:20:00', 1),
(71, 5, '2012-07-18 04:30:00', '2012-07-18 07:10:00', 1),


(72, 1, '2012-07-18 13:00:00', '2012-07-18 14:00:00', 1),
(73, 2, '2012-07-18 13:30:00', '2012-07-18 14:30:00', 1),
(74, 3, '2012-07-18 14:00:00', '2012-07-18 15:00:00', 1),
(75, 4, '2012-07-18 14:30:00', '2012-07-18 15:30:00', 1),
(76, 5, '2012-07-18 18:00:00', '2012-07-18 19:00:00', 1);

: (///), :

uid 1   <--------------------------------------------------------------------------------------...-------->      <-------------------->
uid 2   <----------------------->          <----------------------->    <---->                                          <-------------------->
uid 3       <----------------------->       <------------------------------------------->                                       <-------------------->
uid 4                 <----------------------->      <----------------------->                                                         <-------------------->
uid 5                 <----------------------->              <----------------------->                                                                                              <-------------------->
                      [    1    ]           [2]              [  3  ]    [ 4  ]
                           ^
       We want the start and end times of this overlap

[ ] , . # 1, . # 1 2012-07-18 1:10:00 2012-07-18 2:00:00, :

FromDateTime       | ToDateTime
----------------------------------------
2012-07-18 1:10:00 | 2012-07-18 2:00:00

1:

, , , . , , .

, . , , , , , , :

SELECT   DISTINCT a.ToDateTime
FROM     tbl a
JOIN     tbl b ON a.userid     <> b.userid
              AND a.userid     IN (1,2,3,4,5)
              AND b.userid     IN (1,2,3,4,5)
              AND a.ToDateTime >  b.FromDateTime 
              AND a.ToDateTime <= b.ToDateTime
GROUP BY a.userid,
         a.FromDateTime,
         a.ToDateTime
HAVING   COUNT(DISTINCT b.userid) = 4

:

TODATETIME
-------------------
2012-07-18 02:00:00
2012-07-18 05:00:00
2012-07-18 06:00:00
2012-07-18 03:20:00

SQLFiddle Demo

2:

, , -times , , :

SELECT   b.FromDateTime,
         a.ToDateTime
FROM     (
         SELECT   DISTINCT a.ToDateTime
         FROM     tbl a
         JOIN     tbl b ON a.userid     <> b.userid
                       AND a.userid     IN (1,2,3,4,5)
                       AND b.userid     IN (1,2,3,4,5)
                       AND a.ToDateTime >  b.FromDateTime 
                       AND a.ToDateTime <= b.ToDateTime
         GROUP BY a.userid,
                  a.FromDateTime,
                  a.ToDateTime
         HAVING   COUNT(DISTINCT b.userid) = 4
         ) a
JOIN     (
         SELECT   DISTINCT a.FromDateTime
         FROM     tbl a
         JOIN     tbl b ON a.userid       <> b.userid
                       AND a.userid       IN (1,2,3,4,5)
                       AND b.userid       IN (1,2,3,4,5)
                       AND a.FromDateTime >= b.FromDateTime 
                       AND a.FromDateTime <  b.ToDateTime
         GROUP BY a.userid,
                  a.FromDateTime,
                  a.ToDateTime
         HAVING   COUNT(DISTINCT b.userid) = 4
         ) b ON b.FromDateTime < a.ToDateTime
ORDER BY a.ToDateTime, b.FromDateTime --Ordered for display purposes

:

TODATETIME          | FROMDATETIME        
------------------------------------------
2012-07-18 02:00:00 | 2012-07-18 01:10:00  <-- Most recent FromDateTime
2012-07-18 03:20:00 | 2012-07-18 01:10:00 
2012-07-18 03:20:00 | 2012-07-18 03:10:00  <-- Most recent FromDateTime
2012-07-18 05:00:00 | 2012-07-18 01:10:00 
2012-07-18 05:00:00 | 2012-07-18 03:10:00 
2012-07-18 05:00:00 | 2012-07-18 04:30:00  <-- Most recent FromDateTime 
2012-07-18 06:00:00 | 2012-07-18 01:10:00 
2012-07-18 06:00:00 | 2012-07-18 03:10:00 
2012-07-18 06:00:00 | 2012-07-18 04:30:00 
2012-07-18 06:00:00 | 2012-07-18 05:30:00  <-- Most recent FromDateTime

FromDateTimes . , FromDateTime ToDateTime. , GROUP BY MAX().

SQLFiddle Demo

3:

GROUP BY ToDateTime MAX() FromDateTime, FromDateTimes:

SELECT   MAX(b.FromDateTime) FromDateTime, 
         a.ToDateTime
FROM     (
         SELECT   DISTINCT a.ToDateTime
         FROM     tbl a
         JOIN     tbl b ON a.userid     <> b.userid
                       AND a.userid     IN (1,2,3,4,5)
                       AND b.userid     IN (1,2,3,4,5)
                       AND a.ToDateTime >  b.FromDateTime 
                       AND a.ToDateTime <= b.ToDateTime
         GROUP BY a.userid,
                  a.FromDateTime,
                  a.ToDateTime
         HAVING   COUNT(DISTINCT b.userid) = 4
         ) a
JOIN     (
         SELECT   DISTINCT a.FromDateTime
         FROM     tbl a
         JOIN     tbl b ON a.userid       <> b.userid
                       AND a.userid       IN (1,2,3,4,5)
                       AND b.userid       IN (1,2,3,4,5)
                       AND a.FromDateTime >= b.FromDateTime 
                       AND a.FromDateTime <  b.ToDateTime
         GROUP BY a.userid,
                  a.FromDateTime,
                  a.ToDateTime
         HAVING   COUNT(DISTINCT b.userid) = 4
         ) b ON b.FromDateTime < a.ToDateTime
GROUP BY a.ToDateTime

:

FROMDATETIME        | TODATETIME
-----------------------------------------
2012-07-18 01:10:00 | 2012-07-18 02:00:00
2012-07-18 03:10:00 | 2012-07-18 03:20:00
2012-07-18 04:30:00 | 2012-07-18 05:00:00
2012-07-18 05:30:00 | 2012-07-18 06:00:00

. .

4:

ORDER BY/LIMIT 1 max/min, . , , ( LIMIT 1), :

SELECT   MAX(b.FromDateTime) FromDateTime, 
         a.ToDateTime
FROM     (
         SELECT   DISTINCT a.ToDateTime
         FROM     tbl a
         JOIN     tbl b ON a.userid     <> b.userid
                       AND a.userid     IN (1,2,3,4,5)
                       AND b.userid     IN (1,2,3,4,5)
                       AND a.ToDateTime >  b.FromDateTime 
                       AND a.ToDateTime <= b.ToDateTime
         GROUP BY a.userid,
                  a.FromDateTime,
                  a.ToDateTime
         HAVING   COUNT(DISTINCT b.userid) = 4
         ) a
JOIN     (
         SELECT   DISTINCT a.FromDateTime
         FROM     tbl a
         JOIN     tbl b ON a.userid       <> b.userid
                       AND a.userid       IN (1,2,3,4,5)
                       AND b.userid       IN (1,2,3,4,5)
                       AND a.FromDateTime >= b.FromDateTime 
                       AND a.FromDateTime <  b.ToDateTime
         GROUP BY a.userid,
                  a.FromDateTime,
                  a.ToDateTime
         HAVING   COUNT(DISTINCT b.userid) = 4
         ) b ON b.FromDateTime < a.ToDateTime
GROUP BY a.ToDateTime
ORDER BY TIMESTAMPDIFF(SECOND, MAX(b.FromDateTime), a.ToDateTime) DESC
LIMIT    1

SQLFiddle

SQLFiddle Demo

( ):

, ( ), :

SELECT   MAX(b.FromDateTime) FromDateTime, 
         a.ToDateTime
FROM     (
         SELECT     DISTINCT a.ToDateTime
         FROM       tbl a
         JOIN       tbl b ON a.userid     <> b.userid
                         AND a.ToDateTime >  b.FromDateTime 
                         AND a.ToDateTime <= b.ToDateTime
         CROSS JOIN (SELECT COUNT(DISTINCT userid) totalusers FROM tbl) c
         GROUP BY   a.userid,
                    a.FromDateTime,
                    a.ToDateTime,
                    c.totalusers
         HAVING     COUNT(DISTINCT b.userid) = c.totalusers-1
         ) a
JOIN     (
         SELECT   DISTINCT a.FromDateTime
         FROM     tbl a
         JOIN     tbl b ON a.userid       <> b.userid
                       AND a.FromDateTime >= b.FromDateTime 
                       AND a.FromDateTime <  b.ToDateTime
         CROSS JOIN (SELECT COUNT(DISTINCT userid) totalusers FROM tbl) c
         GROUP BY a.userid,
                  a.FromDateTime,
                  a.ToDateTime,
                  c.totalusers
         HAVING   COUNT(DISTINCT b.userid) = c.totalusers-1
         ) b ON b.FromDateTime < a.ToDateTime
GROUP BY a.ToDateTime
ORDER BY TIMESTAMPDIFF(SECOND, MAX(b.FromDateTime), a.ToDateTime) DESC
LIMIT    1
+6

1D PHP, (Wikipedia). , datetimes : , " ".

: http://pastebin.com/iLwJQEF0

( ), , . "" ( ): , . ( !)

O(n * log n), n - .

:

  • datetime-to-millisecond, . ( .)
  • , / :
    • .
    • , , .
  • , ( , ). .
+2

:

Perl , Set::IntSpan, ( ) intersect, , . , .

(), strtotime("2012-08-27 02:02:02") php. , perl, , .

use Set::IntSpan;

my $r1 = Set::IntSpan->new([ 5 .. 15 ]);
my $r2 = Set::IntSpan->new([ 2 .. 20 ]);

my $i = $r1->intersect($r2);

if ( !$i->empty and ( $i->max - $i->min ) >= 5 ) # criteria
{
print "hit\n"; # $i->max, $i->min are the timestamps you need
}
else
{
print "miss\n";
}

, ( ), date("Y-m-d H:i:s", $timestamp);

:

Perl script PHP , perl script

ps maybe perl pros can wrap the code in a function with 4 arguments? In addition, I understand that this is not an ideal answer to the question, but the idea is a cool idea.

+1
source

Using sel schema from violin (10x sel) ...

The easiest way to do this:

SELECT
    MAX(GREATEST(u1.datetime_start, u2.datetime_start)) AS MeetingStart,
    MIN(LEAST(u1.datetime_end, u2.datetime_end)) AS MeetingEnd
FROM users2 u1
INNER JOIN users2 u2
    ON (u1.datetime_end >= u2.datetime_start AND u1.datetime_start <= u2.datetime_end)
    AND u2.userid != u1.userid
    AND u2.userid IN (3,4,5)
WHERE u1.userid=2
GROUP BY u1.id
HAVING COUNT(DISTINCT u2.userid) = 3 AND MeetingStart < MeetingEnd

Change depending on your situation:

In my example, we have 4 members. n = 4, participants (2,3,4,5)

IN (3,4,5) → last n-1 identifier of the meeting participants

WHERE u1.userid = 2 → id for the first participant in the meeting

HAS AN ACCOUNT (DISTINCT u2.userid) = 3 → n - 1

Can be tested on sqlfiddle

0
source

More articles:


All Articles