Re: Bounded Zone Offset Query

[Robert DiFalco <robert.difalco@xxxxxxxxx>]

On Fri, Jul 10, 2015 at 9:40 AM, John McKown <john.archie.mckown@xxxxxxxxx> wrote:

On Fri, Jul 10, 2015 at 11:15 AM, Robert DiFalco <robert.difalco@xxxxxxxxx> 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';