On Fri, Jul 10, 2015 at 11:15 AM, Robert DiFalco <robert.difa...@gmail.com> wrote:
> 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 > 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. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
