Get row between day groups

Question

Days defined as:

0 - Monday
1 - Tuesday
2 - Wednesday
3 - Thursday
4 - Friday
5 - Saturday
6 - Sunday

I have several groups in database, which have start_day, end_day,...

Part of the SQL is this

SELECT * 
    FROM groups AS pg 
    WHERE ...
    AND pg.start_day <= 6 
    AND pg.end_day >= 6

Now if my group has

start_day: 0
end_day: 4

or

start_day: 5
end_day: 6

the above SQL works completely fine. The problem becomes in this case

start_day: 4
end_day: 5

and

start_day: 6
end_day: 3

when I try to get second group, for example for day 6 since start_day is greater than end_day. What would be the best way to make this work?

Basically I have prices that depend on day of the week, and I need to get the correct price for given day of week.

I don't know if you can change your model at this point but the accepted answer seems a good choice for weekly events like yours http://stackoverflow.com/questions/2836356/best-way-to-store-weekly-event-in-mysql — RubioRic, May 01 '16 at 09:11
@RubioRic that sounds like a good solution, but it would completely break the website at this point. — Bojan Kogoj, May 01 '16 at 09:48
not between wouldn't work due to >= and <= - not makes them < > - so you need to put `NOT (pg.start_day < 0 AND pg.end_day > 4 )` after negation that will result as between. — Michał Zaborowski, May 01 '16 at 12:05

Alex · Accepted Answer · 2016-05-01T11:49:02.220

0

If I understand the problem, it seems that day ranges that wrap around between Sunday and Monday are causing the problem?

The additional clause below should account for this case:

WHERE (
    ( start_day <= end_day 
        and ( start_day <= day_of_week and end_day >= day_of_week )
    ) 
    OR 
    ( start_day > end_day 
        and ( day_of_week >= start_day or day_of_week <= end_day )
    ) 
)

edited May 01 '16 at 11:49

answered May 01 '16 at 10:41

Alex

1,633
12
12

For some reason NOT BETWEEN doesn't work. Also, I had to put the whole ting into extra parentheses, or other conditions were ignored because of OR. – Bojan Kogoj May 01 '16 at 11:37
Sorry about that, I hadn't had a chance to test NOT BETWEEN. I'll edit to write out the condition in full – Alex May 01 '16 at 11:45

score 0 · Answer 2 · answered May 01 '16 at 11:11

Add table with two weeks:

create table test (id int, name varchar(20), ord int);
insert into test values (0, 'Monday', 0);
insert into test values (1, 'Tuesday', 1);
insert into test values (2, 'Wednesday', 2);
insert into test values (3, 'Thursday', 3);
insert into test values (4, 'Friday', 4);
insert into test values (5, 'Saturday', 5);
insert into test values (6, 'Sunday', 6);
insert into test values (0, 'Monday', 10);
insert into test values (1, 'Tuesday', 11);
insert into test values (2, 'Wednesday', 12);
insert into test values (3, 'Thursday', 13);
insert into test values (4, 'Friday', 14);
insert into test values (5, 'Saturday', 15);
insert into test values (6, 'Sunday', 16);

And query with:

select * from test where ord between 4 and 0 or ord between 4 and 10;

In Postgres you could write that as subselect. Other option is stored procedure, but there are some limitations with them as well...

Get row between day groups

2 Answers2