CODEWITHSUNDEEP
mysqlsql
GodFather
GodFather

Reputation: 3146

sql query between two values desc

I have a MySQL table that has two fields start_interval and end_interval both storing int values that represent hours. I'm using this query to select records from that table:

SELECT *
FROM messages
WHERE 23 BETWEEN start_interval AND end_interval;

How can I get values when the start_interval is greater than end_interval? For exemple if start_interval = 22 and end_interval = 5?

Upvotes: 0

Views: 715

Answers (4)

Sam Goldberg
Sam Goldberg

Reputation: 6801

How about:

where 23 between start_interval and if(start_interval>=end_interval,end_interval+24,end_interval)?

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

…
WHERE 23 BETWEEN start_interval AND end_interval
   OR start_interval > end_interval AND
      23 NOT BETWEEN end_interval + 1 AND start_interval - 1

Upvotes: 0

NPE
NPE

Reputation: 500227

How about:

SELECT * FROM messages
WHERE 23 BETWEEN LEAST(start_interval, end_interval) AND
                GREATEST(start_interval, end_interval);

Upvotes: 7

Alp
Alp

Reputation: 29739

Maybe you check for both directions:

SELECT * FROM messages
WHERE
  (23 BETWEEN start_interval AND end_interval) OR
  (23 BETWEEN end_interval AND start_interval);

Upvotes: 2

Related Questions