Re: Bounded Zone Offset Query

Hi Robert:

On Fri, Jul 10, 2015 at 6:55 PM, Robert DiFalco
<robert(dot)difalco(at)gmail(dot)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.