0

I have database with multiple evens.

StartDate      EndDate
-----------------------------
2017-03-18     2017-03-20
2017-10-10     2017-10-10
2017-11-30     2017-12-01

What I need is to get list of used dates which should look like:

2017-03-18
2017-03-19
2017-03-20
2017-10-10
2017-11-30
2017-12-01

These events may be quite a lot and they might be overlapping so the query must be very quick and records grouped by date. If it would be quickier to select unused days it doesn't matter. I just need to find out which days in current year are used.

Pcs
  • 53
  • 1
  • 3
  • MySQL doesn't support analytic functions, so doing this natively isn't straightforward. However, it often isn't necessary—on fetching each row in the resultset, your application can loop between `StartDate` and `EndDate` to construct the desired list of "used days". – eggyal Jan 02 '18 at 14:56
  • Seriously consider handling issues of data display in application code. – Strawberry Jan 02 '18 at 15:21

1 Answers1

1

The easiest way to do this is by making yourself a dates table with a row for every day in it.

Then you can do this

 SELECT DISTINCT d.day
   FROM dates d
   JOIN mytable t ON d.day >= t.StartDate AND d.day <= t.EndDate
  ORDER BY d.day

and get an enumeration of all the days, meeting your need.

There are ways to generate the dates table on the fly, but they are a notorious pain in the neck. You might want to look up that sort of thing.

(Shameless self-promo ... here's a possible way to do it. https://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/)

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • [how-to-populate-a-table-with-a-range-of-dates](https://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates) – Patrick Artner Jan 02 '18 at 15:10