Re: Bounded Zone Offset Query

On Fri, Jul 10, 2015 at 11:15 AM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)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