mysql query between days and calculate days between dates

Question

i need to get the id and days count between days.

INSERT INTO Events
    (`ID`, `START`, `END`)
VALUES
    (2313, '2019-07-29', '2019-08-10'),
    (41, '2019-06-22', '2019-07-01'),
    (2540, '2019-06-22', '2019-07-02'),
    (2234, '2019-06-28', '2019-07-12'),
    (2634, '2019-06-30', '2019-07-04'),
    (53, '2019-06-30', '2019-07-02'),
    (1869, '2019-06-30', '2019-07-15'),
    (2132, '2019-07-30', '2019-08-10')
;

my query is

SELECT `ID`
  FROM Events 
 WHERE `START` BETWEEN '2019-07-01' AND '2019-07-31' 
   AND `END` BETWEEN '2019-07-01' AND '2019-07-31';

i need output as

2313 - 3
41 - 1
2540 - 2
2234 - 12
.....

please help me.

sorry, i need output as 2313 - 3, 41 - 1, 2540 - 2, 2234 - 12 — Yathav Sri Technology
– Yathav Sri Technology, Commented Aug 1, 2019 at 7:59
@YathavSriTechnology Second time from where comes 2313 - 3 (this 3 ??) — MorganFreeFarm
– MorganFreeFarm, Commented Aug 1, 2019 at 8:00

Barbaros Özhan · Accepted Answer · 2019-08-01 08:14:02Z

4

GREATEST returns max value from list, LEAST - min

select ID, 
       DATEDIFF(LEAST(`END`,'2019-07-31'), GREATEST(`START`, '2019-07-01')) + 1
from events

edited Aug 1, 2019 at 8:14

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Aug 1, 2019 at 8:06

splash58

26.2k3 gold badges25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Yathav Sri Technology Over a year ago

Thanks @splash58, i got exact output when i changed the date condition i get in-correct data. query is "select ID, DATEDIFF(LEAST(END,'2019-06-30'), GREATEST(START, '2019-06-01')) from events"

Barbaros Özhan Over a year ago

@YathavSriTechnology consider this

Yathav Sri Technology Over a year ago

Finally i got the result by passing condition. "select ID, DATEDIFF(LEAST(END,'2019-08-31'), GREATEST(START, '2019-08-01')) + 1 as dDate from events having dDate > 0;"

splash58 Over a year ago

@YathavSriTechnology Very well. I did not think about where condition :(

tolgakaragol · Accepted Answer · 2019-08-01 08:01:46Z

1

use DATEDIFF() function.

SELECT `id`, DATEDIFF(`start`, `end`) 
FROM `events` 
WHERE 
 `start` BETWEEN '2019-07-01' AND '2019-07-31' AND
 `end` BETWEEN '2019-07-01' AND '2019-07-31';

answered Aug 1, 2019 at 8:01

tolgakaragol

6036 silver badges13 bronze badges

2 Comments

Barbaros Özhan Over a year ago

Unfortunately, this does not provide a solution for OP's question. Here's the Demo

Yathav Sri Technology Over a year ago

when i change the date in query it showing negative value. i need output for between the date criteria not to show all the rows. dbfiddle.uk/…

Collectives™ on Stack Overflow

mysql query between days and calculate days between dates

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related