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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

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 anyone help me with this, please?

Thank you in advance, hug.

EDIT

imagine that I have a barracao to save boats and I had a boat saved it from 01/01/2014 until 01/31/2014 and during that period the value of the daily rate changed twice, from 02/12/2013 until 15/01/2014 the daily cost X and from 01/16/2014 until 02/15/2014 the 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 rate.

I do not know if the example helps or disrupts.

mysql sql

asked by anonymous 26.02.2015 / 14:42

2 answers

1

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

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 order specified in DATEDIFF the result may be negative);

There are also other functions like TIMEDIFF , TIMESTAMPDIFF

References:

How to Calculate difference between DATES (TIMESTAMP, DATE or TIME) in Mysql

Documentation

26.02.2015 / 14:53

Session error in user-level validation How to call another WP application

score 0 · Accepted Answer

At first, you have three cases to consider:

The beginning of periodo2 , which I'll call p2 , is before or equal to the beginning of periodo1 , now called p1 . In this case, the response is the number of days between the start of p1 and the end of p1 or p2 , whichever is less.

The end of p2 is after or equal to the end of p1 , in which case the answer is the beginning of p1 or p2 , whichever is greater, subtracted from the end of p1 . / li>

Both extremes are different, so one must decide which beginning is greater and which end is smaller. The difference of these higher beginning and lower end is the number of days in common.

So, in pseudocode, it would look 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 have not been confused.