Find all rows with timestamp range inside of another timestamp range

Question

I need to select all rows where start and due columns range falls into another range. All times are given in unix timestamp format.

The following query works, except when start < :weekstart or due > :weekend.

$week_start = (new DateTimeImmutable())->setTimestamp($_REQUEST['startdatets']);
$week_start_ts = $week_start->getTimestamp();
$week_end = $week_start->modify('+6 days')->setTime(23,59,59);
$week_end_ts = $week_end->getTimestamp();

$query = "  SELECT *

        FROM        schedules

        WHERE       ((start BETWEEN :weekstart AND :weekend) OR (due BETWEEN :weekstart AND :weekend))

        ORDER BY    start
";
try { $stmt = $dbh->prepare($query); $stmt->execute(array(':weekstart' => $week_start_ts, ':weekend' => $week_end_ts)); } catch(PDOException $ex) { echo 'Query failed: ' . $ex->getMessage(); exit; }

I need both to work if possible Please help.

Edit your question and provide sample data and desired results. — Gordon Linoff, Dec 08 '16 at 03:52
Possible duplicate of [Determine Whether Two Date Ranges Overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) — Juan Carlos Oropeza, Dec 08 '16 at 04:20
I voted to close this question as unclear what you are asking. — Tim Biegeleisen, Dec 08 '16 at 04:30
@TimBiegeleisen: what exactly was unclear? I clearly specified what I need. — Alex G, Dec 08 '16 at 04:33

score 1 · Accepted Answer · answered Dec 08 '16 at 04:13

1

Ahh, so you you're missing cases where both start < :weekstart AND end > :weekend - neither is in between, but the week overlaps the range. There's a nice logic for it.

Search for periods WHERE start < :weekend AND end > :weekstart

answered Dec 08 '16 at 04:13

shudder

2,076
2
20
21

You sure? Look carefully what is compared. – shudder Dec 08 '16 at 04:17
1

This will work, just try it. Check duplicate link for detail explanation. – Juan Carlos Oropeza Dec 08 '16 at 04:23

Find all rows with timestamp range inside of another timestamp range

1 Answers1