How to check if time is not between two time entries within select-CodePudding

I have two columns in PostgreSQL that store time values (H:M:S) and I use SQLAlchemy. How can I make a select using SQLAlchemy for values that are not between the two values?

Example 1:

current time in the interrogation = 10:00:01
start_time = 16:00:12
end_time = 22:00:00 Return the value because is outside of this time interval

current time = 10:00:01
start_time = 07:00:12
end_time = 22:00:00 Return nothing because is within this interval.

CodePudding user response：

SELECT CASE
         WHEN start_time <= current_time and end_time >= current_time
         THEN current_time
         ELSE NULL :: time
       END 
  FROM your_table

CodePudding user response：

Use the negation operator NOT with BETWEEN.

select '10:00:01'::time not between '16:00:12'::time and '22:00:00'::time;
 ?column? 
----------
 t

 select '10:00:01'::time not between '07:00:12'::time and '22:00:00'::time;
 ?column? 
----------
 f

To select from the table you would want something like:

select time_col from the_table where time_col not between '16:00:12'::time and '22:00:00'::time;

--or

select time_col from the_table where time_col not between '07:00:12' and '22:00:00'::time;

As to SQLAlchemy see Between:

from sqlalchemy import between

stmt = select(users_table).where(between(users_table.c.id, 5, 7))

and I believe to negate that it would be something like:

select(users_table).where(~between(users_table.c.id, 5, 7))