Oracle, vyhodnocení časových podmínek

Dneska jsem řešil (pro mne) zajímavý případ - potřeboval jsem v Oracle databázi postavit dotaz s následujícími parametry:

• zkontrolovat, že dva určité záznamy mají hodnotu DATE mezi 17:00 včerejšího dne a 7:00 dnešního dne,
• jako výstup dotazu mít hodnotu true nebo false (v Oraclu 1 a 0).

Dejme tomu, že máme následující tabulku

CREATE TABLE result_log (
    code VARCHAR2(20),
    logged DATE
  );

do které vložíme  dva záznamy odpovídající našemu intervalu (17:00-7:00):

INSERT INTO result_log VALUES
  ('codeA', TRUNC(sysdate - 1) + 22 / 24);
INSERT INTO result_log VALUES
  ('codeB', TRUNC(sysdate) + 5 / 24);

Výsledek by měl vypadat nějak takto:

SELECT code, TO_CHAR(logged,
                     'YYYY-MM-DD HH24:MM:SS')
             AS logged
  FROM result_log;

CODE                 LOGGED
-------------------- -------------------
codeA                2012-04-05 22:04:00
codeB                2012-04-06 05:04:00

Požadované záznamy (codeA, codeB) vybereme následujícím dotazem a zároveň je ohodnotíme, že jsou/nejsou z daného časového intervalu (1 = jsou, 0 = nejsou):

SELECT
  CASE
    WHEN (logged BETWEEN
        (TRUNC(sysdate - 1) + 17 / 24) AND
        (TRUNC(sysdate) + 7 / 24))
    THEN 1
    ELSE 0
  END AS eveluation
FROM result_log
WHERE code IN ('codeA', 'codeB');

EVELUATION
----------
         1
         1

Jelikož nad výsledky potřebujeme v podstatě provést logickou konjunkci (AND) můžeme k tomuto účelu použít funkci DECODE. Výsledný dotaz pak vypadá následovně:

SELECT DECODE(SUM(eveluation), COUNT(*), 1, 0)
  AS evaluation
FROM
  (SELECT
    CASE
      WHEN (logged BETWEEN
        (TRUNC(sysdate - 1) + 17 / 24) AND
        (TRUNC(sysdate) + 7 / 24))
      THEN 1
      ELSE 0
    END AS eveluation
  FROM result_log
  WHERE code IN ('codeA', 'codeB')
  );

EVALUATION
----------
         1

Elegantní na tomto řešení je, že stačí pouze přidat do klauzule WHERE další kód (codeC) a vše ostatní bez problémů dál funguje.