Asked  7 Months ago    Answers:  5   Viewed   348 times

I have the following data;

ID  startDate               endDate
-----------------------------------------------
1   2010-03-01 10:00:00     2010-03-01 12:00:00
2   2010-03-01 12:30:00     2010-03-01 15:30:00
3   2010-03-01 15:30:00     2010-03-01 18:30:00

What I want to do is check that a start and end date don't fall inside the startDate and endDate ranges in my data.

So for example, the following would be OK;

startDate               endDate
-----------------------------------------------
2010-03-01 12:00:00     2010-03-01 12:30:00
2010-03-01 18:30:00     2010-03-01 21:00:00

but the following dates would fail, as they would overlap;

startDate               endDate
-----------------------------------------------
2010-03-01 09:00:00     2010-03-01 13:00:00 (overlaps ID 1)
2010-03-01 10:30:00     2010-03-01 11:00:00 (overlaps ID 1)
2010-03-01 18:00:00     2010-03-01 19:00:00 (overlaps ID 3)

I'm pulling my hair out because I can get one or two of the above 3 test date ranges to fail but not all of them.

I am using MySQL.

 Answers

82

A query to select overlaps (I'd name the columns startTime & endTime though, as Time seems important...):

WHERE 
<start> < endDate
AND
<end> > startDate
Wednesday, March 31, 2021
 
Dev
answered 7 Months ago
Dev
48

I'm using PHP/MySQL and am going on the basis that a query can be run and if there 'are' matching results then, fail, if there 'arent' matching results then insert.

Well, try this. Here :date: is the date of the entry you are going to add, and :start-time: and :finish-time: are its start and finish times respectively.

SELECT EXISTS (
    SELECT
        1
    FROM
        TableName
    WHERE
        `date` = :date: AND
        ( :start-time: BETWEEN startTime AND finishTime OR
          :finish-time: BETWEEN startTime AND finishTime OR
          startTime BETWEEN :start-time: AND :finish-time:
          )
) AS `Clash`
Wednesday, March 31, 2021
 
zhartaunik
answered 7 Months ago
79

Here you go:

Select * from calendar_date as ,c where LEAST(c.end, end) - GREATEST(c.start, start) > 0
Saturday, May 29, 2021
 
Corne
answered 5 Months ago
78

You could extend the DateTime class and implement createFromFormat() yourself like this:-

class MyDateTime extends DateTime
{
    public static function createFromFormat($format, $time, $timezone = null)
    {
        if(!$timezone) $timezone = new DateTimeZone(date_default_timezone_get());
        $version = explode('.', phpversion());
        if(((int)$version[0] >= 5 && (int)$version[1] >= 2 && (int)$version[2] > 17)){
            return parent::createFromFormat($format, $time, $timezone);
        }
        return new DateTime(date($format, strtotime($time)), $timezone);
    }
}

$dateTime = MyDateTime::createFromFormat('Y-m-d', '2013-6-13');
var_dump($dateTime);
var_dump($dateTime->format('Y-m-d'));

This will work in all versions of PHP >= 5.2.0.

See here for a demo http://3v4l.org/djucq

Friday, August 20, 2021
 
pop
answered 2 Months ago
pop
90

I figured this out by modifying the query given in the solution for getting all dates.

The following query returns all dates, and counts of the IDs if any records exist:

select d.date, count(v.id) from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) d
left join visitors v on d.date = v.date
where d.date between '2016-06-01' and '2016-06-30'
group by d.date
order by d.date

Courtesy for getting the dates range goes to @mark-bannister and a simple join on the query matching for results, and sorting gets the solution.

Thursday, August 26, 2021
 
Hexaholic
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :