CODEWITHSUNDEEP
mysqlgaps-and-islands
user15512354
user15512354

Reputation:

Get from certain sql range

I have the following video table:

create table videos(
  title text,
  date_range text);
  
insert into videos(title, date_range) values 
('Title1', '["2021-03-18 - 2021-03-20", "2021-03-01 - 2021-03-05", "2021-03-15"]'),
('Title2', '["2021-02-12 - 2021-02-13", "2021-02-02 - 2021-02-06","2021-02-10"]');

+--------+----------------------------------------------------------------------+
| title  |                date_range                                            |
+--------+----------------------------------------------------------------------+
| Title1 | ["2021-03-18 - 2021-03-20", "2021-03-01 - 2021-03-05", "2021-03-15"] |
| Title2 | ["2021-02-12 - 2021-02-13", "2021-02-02 - 2021-02-06","2021-02-10"]  |
+--------+----------------------------------------------------------------------+

How to get videos from 2021-03-18 - 2021-03-19?

So the result table should look like this:

+--------+----------------------------------------------------------------------+
| title  |                date_range                                            |
+--------+----------------------------------------------------------------------+
| Title1 | ["2021-03-18 - 2021-03-20", "2021-03-01 - 2021-03-05", "2021-03-15"] |
+--------+----------------------------------------------------------------------+

Here you have fiddle with this table, but with no result, because I do not know how to achieve this. Thanks in advance!

Upvotes: 1

Views: 21

Answers (1)

Strawberry
Strawberry

Reputation: 33945

A normalised schema might be somewhat as follows:

id title  date_range_start date_range_end
 1 Title1 2021-03-18       2021-03-20
 2 Title1 2021-03-01       2021-03-05
 3 Title1 2021-03-15       2021-03-15
 4 Title2 2021-02-12       2021-02-13
 5 Title2 2021-02-02       2021-02-06
 6 Title2 2021-02-10       2021-02-10

Upvotes: 1

Related Questions