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
I'm not sure exactly what :utSecondsOfDay really is. I guess it is an integer which is a "time" value, such as "seconds after midnight" and thus would range be from 0 to 24*60*60=86400 (actually 86399, I guess). In this notation, 10 am would be 10*60*60 or 36000 and 10pm would be 22*60*60 or 79200. How about calculating, in your application code, two different values: utcSecondsLower and utSecondsHigher. utcSecondsLower would be 36000-utcSecondsOfDay. utcSecondsHigher would be 79200-utSecondsOfDay. Change the SELECT to be:
SELECT *
FROM devices
WHERE ut_offsec_secs BETWEEN :utcSecondsLower AND :utcSecondsHigher;
I am not sure, but I think that is legal. Or maybe it gives you another approach.
--
Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
