count how many days a date range has in another date range

Question

Good morning, I’m doing a query (mysql) and I have four dates periodo1inicio, periodo1final, periodo2inicio and periodo2final. I need to count how many days of period 2 is contained in period 1.

Can someone give me a hand with this, please?

Thanks in advance, hug.

EDIT

imagine that I have a shed to keep boats and I had a boat saved there from 01/01/2014 until 31/01/2014 and during this period the value of daily changed twice, from 02/12/2013 until 15/01/2014 daily cost X and from 16/01/2014 until 15/02/2014 daily cost Y.

i need to know that 15 days I charge the daily X and the rest of the days I charge the Y fee.

I don’t know if the example helps or disturbs.

Answer 1

You can count the days difference between two dates in Mysql using the function DATEDIFF.

SELECT DATEDIFF(CURDATE(), '2015-01-01'); //Retorna 56 (Base: 26/02/2015);

For your case: SELECT DATEDIFF(periodo2inicio, periodo1inicio) FROM ....;

If you do not get the expected result, please let us know. (Depending on the dates and the order specified in the DATEDIFF the result may be negative);

There are also other functions such as TIMEDIFF, TIMESTAMPDIFF

References:

How to Calculate Date Difference (TIMESTAMP, DATE or TIME) in Mysql

Documentation

Answer 2

At first, you have three cases to consider:

1. The beginning of periodo2, which I will call p2, is before or equal to the beginning of the periodo1, called from now on as p1. In this case, the answer is the number of days between the beginning of p1 and the end of p1 or p2, whichever is less.
2. The end of p2 is after or equal to the end of the p1, in which case the answer is the beginning of p1 or p2, whichever is greater, subtracted from the end of p1.
3. Both extremes are different, so one must decide which beginning is larger and which end is smaller. The difference of these major beginning and minor end is the number of days in common.

So, in pseudocode, it would be something like:

d1.inicio = periodo1inicio
d1.fim = periodo1fim
d2.inicio = periodo2inicio
d2.fim = periodo2fim
//Caso 1
se d2.inicio = d1.inicio
  entao se d1.fim <= d2.fim
          entao return DATEDIFF(d1.inicio, d1.fim)
          senao return DATEDIFF(d1.inicio, d2.fim)
  fim-se
fim-se
//Caso 2
se d2.fim = d1.fim
  entao se d1.inicio <= d2.inicio
          entao return DATEDIFF(d2.inicio, d1.fim)
          senao return DATEDIFF(d1.inicio, d1.fim)
  fim-se
fim-se
//Caso 3
se d2.inicio < d1.inicio
  entao se d2.fim < d1.fim
          entao return DATEDIFF(d1.inicio, d2.fim)
          senao return DATEDIFF(d1.inicio, d1.fim)
        fim-se
  senao se d2.fim < d1.fim
          entao return DATEDIFF(d2.inicio, d2.fim)
          senao return DATEDIFF(d2.inicio, d1.fim)
        fim-se
  fim-se
fim-se

I hope you’re not confused.