Select some datetime from base

I have a 2 table in my base. The first table is reservation table. The start_ts and end_ts are time when start and end the reservation of desk (desk_id):

[Reservation_table]

The second is motion, which come from sensors motion.

[Motion_table]

This 2 tables are connecting that the sensors are start save to the base when someone come to desk (desk_id). The reading from sensors are saving to the base as long as someone is sitting to the desk. The reading from the sensors for the most case are between reservation time (start and end time from reservation table). Sometime the employee come to office without reservation (don’t have a start and end time) but does some reading from motions sensors.

I would like to receive a data which contain a datetime which are not between reservation time. For this moment I have:

query = """
SELECT reservation.user_id, motion.desk_id, reservation.start_ts, reservation.end_ts, 
reservation.confirm_ts, reservation.cancel_ts, 
motion.datetime
FROM reservation
RIGHT JOIN motion ON (reservation.desk_id = motion.desk_id
AND EXTRACT(YEAR FROM reservation.start_ts) = EXTRACT(YEAR FROM motion.datetime)
AND EXTRACT(MONTH FROM reservation.start_ts) = EXTRACT(MONTH FROM motion.datetime)
AND EXTRACT(DAY FROM reservation.start_ts) = EXTRACT(DAY FROM motion.datetime)
AND EXTRACT(HOUR FROM reservation.start_ts) = EXTRACT(HOUR FROM motion.datetime)
AND EXTRACT(MINUTE FROM reservation.start_ts) <= EXTRACT(MINUTE FROM motion.datetime))
ORDER BY motion.datetime;
"""

But this code join me this 2 tables and i don’t want to do this. I would like to have only a datetime which are not between reservation. It would looks like selected motion time.

Answer

You need to get (select) sensor data (from motion) for which (where) there’s no (not exists) reservation (select * from reservation) of the same desk (where motion.desk_id = reservation.desk_id) at this point of time (and motion.datetime_ between reservation.start_ts and reservation.end_ts). Here it is composed:

select *
from motion
where not exists (
  select 'no reservation for this time point'
  from reservation
  where motion.desk_id = reservation.desk_id
    and motion.datetime_ between reservation.start_ts and reservation.end_ts
)

SQL is quite naturally expresses what you need.

And a side notice: do not use keywords as identifiers. So replace datetime with some other name. It is a datetime of something, then you may use event_datetime for example.