Thread: Bounded Zone Offset Query
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 devicesWHERE :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 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 devicesWHERE :utcSecondsOfDay + utc_offset_secs BETWEEEN 10am AND 10pmIn 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
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
On Fri, Jul 10, 2015 at 9:40 AM, John McKown <john.archie.mckown@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 devicesWHERE :utcSecondsOfDay + utc_offset_secs BETWEEEN 10am AND 10pmIn 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?ThanksI'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 devicesWHERE 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 dwhere (now() at time zone 'UTC' + make_interval(hours := d.utc_offset))::timeBETWEEN time '10:00' AND time '22:00';
Hi Robert:
On Fri, Jul 10, 2015 at 6:55 PM, Robert DiFalco
<robert.difalco@gmail.com> wrote:
>>> 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.
.....
> 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';
....
It seems you have hours in utc-offset, and then you work in seconds in the day.
Your problem to turn it around is the (now+offset) cast to time,
which, working in hours only, is more or less equivalent to
(hours_now+hours_offset+24) mod 24, can materialize into two distinct
utc_offset intervals. I'll try to make an example.
If utc-now is 2:00, and utc-offset goes from -12 to +12, you need to
select -12 to -4 ( which added to 2 are -10 to -2 or 14 - 22) and 8
to 12 ( which translate to 10-14).
You are going to have this problem even if you use utc-offset 0-24 (
in this case utc-now=2 works, you would use 8 to 20 for utc-offset,
but if utc-now where 20:00 you would need to query for utc-offset in
0-2 and 14-24 ( which translate to 20-22 and 34-44=10-20 ).
If I were to do this, I would make the host code emit the appropiate
ranges ( either one or two ) as all my machines are NTP synced, so
now() is the same as time(). If You really need the database
timestamps you can do a small function and do it there. If the db is
properly analyzed and indexed on utc-time you can even emit the dual
ranges always. Something like this ( I'll use some placeholders ):
secs_now=utc seconds of now, you can get this casting to times and
doing some fancy stuff, I've tested this:
# select extract('epoch' from (cast(now() at time zone 'UTC' as
time)-'00:00'::time)) as secs_now;
secs_now
--------------
33741.008092
(1 row)
Now, you can use seconds instead of 10:00, 20:00 easily, either use
the above or just multiply by 3600 if your original values are
seconds, that would be 10*3600, 22*3600, let's call them lo_secs and
hi_secs. Given there are 86400=24*3600 secs per day , and assuming
utc-offset is in the range -12..24 ( to cover everything ) you need to
select:
You want to select ( utc_offset*3600 + secs_now + 86400 ) % 86400
between lo_secs and hi_secs.
The +86400 is to bring everything to possitive ( I never do modulus on
negative, behaviour depends on languages ). Anyway. Utc_offset*3600 is
in the range -43200, 86400, secs_now is in 0-86400, so with the
addition the sum range is in 43200-86400*3, so you can get rid of the
modulus operation by doing:
( utc_offset*3600 + secs_now + 86400 ) - 0 * 86400 between
lo_secs and hi_secs
or ( utc_offset*3600 + secs_now + 86400 ) - 1 * 86400 between
lo_secs and hi_secs
or ( utc_offset*3600 + secs_now + 86400 ) - 2 * 86400 between
lo_secs and hi_secs
Which you can then simplify and turn into conditions on utc-offset:
( utc_offset*3600 + secs_now + 86400 ) between lo_secs and hi_secs
or ( utc_offset*3600 + secs_now ) between lo_secs and hi_secs
or ( utc_offset*3600 + secs_now - 86400 ) between lo_secs and hi_secs
...
utc_offsets between (lo_secs-secs_now-86400)/3600 and
(hi_secs-secs_now-86400)/3600
or utc_offsets between (lo_secs-secs_now)/3600 and
(hi_secs-secs_now)/3600
or utc_offsets between (lo_secs-secs_now+86400)/3600 and
(hi_secs-secs_now+86400)/3600
Its a little tricky, but can be easily packed into a procedure, and if
the db is properly set up it can get rid of the empty ranges with just
a simple index check. Also, if you start with lo-hours and hi-hours,
you can make it more pallatable by calculating hours-now instead of
secs-now by dividing by 3600 and then you have
utc_offsets between (lo_hours-hours_now-24) and (hi_hours-hours_now-24)
or utc_offsets between (lo_hours-hours_now) and (hi_hours-hours_now)
or utc_offsets between (lo_hours-hours_now+24) and (hi_hours-hours_now+24)
(note, if your utc-offset range is just 24hr instead of 36hr like I've
assumed you can get by with just two conditions, but they are a bit
harder to obtain as you will need to massage the range ( been there,
done this, just not in sql, also, if you are in procedure and
precalculate the ranges for the three between and you know your
utc-offsets ranges you will notice int the 24 hrs range case one of
the three can always be proved empty ( i.e., if your ranges are -12 to
12, one of the three betweens will have hi-limit less than -12 or low
limit greater than 12 )
Francisco Olarte.