On Fri, Jul 10, 2015 at 9:40 AM, John McKown <john.archie.mck...@gmail.com>
wrote:
> 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
>
Thanks John, let me revise my original query to give a CORRECT and working
example, maybe this will help. I've created a query that actually works,
it's just ugly and I'd like to figure out how to make it like the example
you gave (i.e. no math on the utc_offset field, just comparisons).
select *
from devices d
where (now() at time zone 'UTC' + make_interval(hours :=
d.utc_offset))::time
BETWEEN time '10:00' AND time '22:00';
