CODEWITHSUNDEEP
sql
Simo
Simo

Reputation: 728

Check if a time period is included in another time period

In my db i have many record, with start date and length of that time of period.

For example

id start_date           lenght
1  2013-01-01 00:00:00   20
2  2013-02-30 00:00:00   10
3  2013-01-20 00:00:00   3

So i can easily get the end date.

Now if the user gave me any period of time, how can I control if that period is included in one of the time period that I have in the db?

Thank you.

Upvotes: 0

Views: 1798

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can get the list using a where clause and the date functions:

select *
from t
where XXX between start_date and date_add(start_date, interval length day);

EDIT:

The above is for one date. If the user gives two date, XXX and YYY, then this is what you want for any overlap:

select *
from t
where XXX <= date_add(start_date, interval length day) and
      YYY >= start_date;

That is, the period the user gives you starts before the end of the interval and the period ends after the start of the interval.

Upvotes: 2

Related Questions