Bounded Zone Offset Query

[Robert DiFalco <robert.difalco@xxxxxxxxx>]

I have a table something like this:

CREATE TABLE devices (
  owner_id        BIGINT NOT NULL, 
  utc_offset_secs INT,
  PRIMARY KEY (uid, platform),
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
);

I want to do a query from an application that returns all devices who's time is between 10am or 10pm for a given instant in time.

For example:

SELECT * 

FROM devices

WHERE :utcSecondsOfDay + utc_offset_secs BETWEEEN 10am AND 10pm

In the above query assume the correct "seconds of day" values for 10am and 10pm. The problem is that I have to do addition on each record to do the above query and I can't imagine that would be efficient. Also I think it this example query will only work in some cases. For example what if the utcSecondsOfDay is 360 (i.e. 1am) and the utc_offset_secs is -5 hours?

Thanks