Question 114134 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to check if time is not between two time entries within select

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

2:

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

2

Answers


  1. 0 
    SELECT CASE
         WHEN start_time <= current_time and end_time >= current_time
         THEN current_time
         ELSE NULL :: time
       END 
  FROM your_table
    Login or Signup to reply.
  2. 0

    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))

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search