Re: [GENERAL] Bounded Zone Offset Query
Hi Robert: On Fri, Jul 10, 2015 at 6:55 PM, Robert DiFalco 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bounded Zone Offset Query
On Fri, Jul 10, 2015 at 9:40 AM, John McKown wrote: > On Fri, Jul 10, 2015 at 11:15 AM, Robert DiFalco > wrote: > >> I have a table something like this: >> >> CREATE TABLE devices ( >> owner_idBIGINT 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';
Re: [GENERAL] Bounded Zone Offset Query
On Fri, Jul 10, 2015 at 11:15 AM, Robert DiFalco wrote: > I have a table something like this: > > CREATE TABLE devices ( > owner_idBIGINT 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
[GENERAL] Bounded Zone Offset Query
I have a table something like this: CREATE TABLE devices ( owner_idBIGINT 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