Bounded Zone Offset Query

Поиск
Список
Период
Сортировка
От Robert DiFalco
Тема Bounded Zone Offset Query
Дата
Msg-id CAAXGW-wJPXKapyNC3-S=z9MKohR9632Z85m+fgs0GAuj6a2JdA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Bounded Zone Offset Query
Список pgsql-general
Дерево обсуждения
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 

В списке pgsql-general по дате отправления: