CODEWITHSUNDEEP
sqloracle-database
Lucavox
Lucavox

Reputation: 1

Data comparison on minutes

I am running the following query.

select count(*), TRUNC(creation_date, 'MI')
from "AUDIT"
where MODULE_NAME = 'PERSONAL_PLUGIN'
and CREATION_DATE > TO_TIMESTAMP_TZ('06/07/2022 05:40 +02:00','DD/MM/YYYY HH24:MI TZH:TZM')
and CREATION_DATE < TO_TIMESTAMP_TZ('06/07/2022 06:20 +02:00','DD/MM/YYYY HH24:MI TZH:TZM')
group by TRUNC(creation_date, 'MI')
order by TRUNC(creation_date, 'MI') asc

Obtaining the following result:

enter image description here

What I would like to achieve is to check when there are no values ​​in the counts. For example from 05:50:00 to 06:15:00 there are no counts and I would like to view the data. This calculation should be done from 00:00 to 23:59 on the same day minute by minute.

Upvotes: 0

Views: 40

Answers (1)

MT0
MT0

Reputation: 168212

You can view the times when there was no data by using a row-generator to generate all the minutes and then joining to that:

SELECT count(a.module_name),
       t.time AS creation_date
FROM   ( SELECT TIMESTAMP '2022-07-06 05:40:00 +02:00'
                 + (LEVEL - 1) * INTERVAL '1' MINUTE AS time
         FROM   DUAL
         CONNECT BY 
                TIMESTAMP '2022-07-06 05:40:00 +02:00'
                 + (LEVEL - 1) * INTERVAL '1' MINUTE
                 < TIMESTAMP '2022-07-06 06:20:00 +02:00'
       ) t
       LEFT OUTER JOIN "AUDIT" a
       ON     t.time <= a.creation_date
          AND a.creation_date < t.time + INTERVAL '1' MINUTE
          AND a.MODULE_NAME = 'PERSONAL_PLUGIN'
GROUP BY t.time
ORDER BY t.time asc

Which, for the sample data:

CREATE TABLE "AUDIT" (module_name, creation_date) AS
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 05:41:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 35 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 05:42:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 35 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 05:43:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 43 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 05:44:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 38 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 05:45:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 32 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 05:46:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 26 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 05:47:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 42 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 05:48:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 40 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 05:49:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 37 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 05:50:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 14 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 06:15:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 50 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 06:16:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 68 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 06:17:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 56 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 06:18:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 46 UNION ALL
SELECT 'PERSONAL_PLUGIN', TIMESTAMP '2022-07-06 06:19:00 +02:00' FROM DUAL CONNECT BY LEVEL <= 45;

Outputs:

COUNT(A.MODULE_NAME) CREATION_DATE
0 06-JUL-22 05.40.00.000000000 +02:00
35 06-JUL-22 05.41.00.000000000 +02:00
35 06-JUL-22 05.42.00.000000000 +02:00
43 06-JUL-22 05.43.00.000000000 +02:00
38 06-JUL-22 05.44.00.000000000 +02:00
32 06-JUL-22 05.45.00.000000000 +02:00
26 06-JUL-22 05.46.00.000000000 +02:00
42 06-JUL-22 05.47.00.000000000 +02:00
40 06-JUL-22 05.48.00.000000000 +02:00
37 06-JUL-22 05.49.00.000000000 +02:00
14 06-JUL-22 05.50.00.000000000 +02:00
0 06-JUL-22 05.51.00.000000000 +02:00
0 06-JUL-22 05.52.00.000000000 +02:00
0 06-JUL-22 05.53.00.000000000 +02:00
0 06-JUL-22 05.54.00.000000000 +02:00
0 06-JUL-22 05.55.00.000000000 +02:00
0 06-JUL-22 05.56.00.000000000 +02:00
0 06-JUL-22 05.57.00.000000000 +02:00
0 06-JUL-22 05.58.00.000000000 +02:00
0 06-JUL-22 05.59.00.000000000 +02:00
0 06-JUL-22 06.00.00.000000000 +02:00
0 06-JUL-22 06.01.00.000000000 +02:00
0 06-JUL-22 06.02.00.000000000 +02:00
0 06-JUL-22 06.03.00.000000000 +02:00
0 06-JUL-22 06.04.00.000000000 +02:00
0 06-JUL-22 06.05.00.000000000 +02:00
0 06-JUL-22 06.06.00.000000000 +02:00
0 06-JUL-22 06.07.00.000000000 +02:00
0 06-JUL-22 06.08.00.000000000 +02:00
0 06-JUL-22 06.09.00.000000000 +02:00
0 06-JUL-22 06.10.00.000000000 +02:00
0 06-JUL-22 06.11.00.000000000 +02:00
0 06-JUL-22 06.12.00.000000000 +02:00
0 06-JUL-22 06.13.00.000000000 +02:00
0 06-JUL-22 06.14.00.000000000 +02:00
50 06-JUL-22 06.15.00.000000000 +02:00
68 06-JUL-22 06.16.00.000000000 +02:00
56 06-JUL-22 06.17.00.000000000 +02:00
46 06-JUL-22 06.18.00.000000000 +02:00
45 06-JUL-22 06.19.00.000000000 +02:00

If you just want the times when there is no data then use a HAVING clause:

SELECT t.time AS creation_date
FROM   ( SELECT TIMESTAMP '2022-07-06 05:40:00 +02:00'
                 + (LEVEL - 1) * INTERVAL '1' MINUTE AS time
         FROM   DUAL
         CONNECT BY 
                TIMESTAMP '2022-07-06 05:40:00 +02:00'
                 + (LEVEL - 1) * INTERVAL '1' MINUTE
                 < TIMESTAMP '2022-07-06 06:20:00 +02:00'
       ) t
       LEFT OUTER JOIN "AUDIT" a
       ON     t.time <= a.creation_date
          AND a.creation_date < t.time + INTERVAL '1' MINUTE
          AND a.MODULE_NAME = 'PERSONAL_PLUGIN'
GROUP BY t.time
HAVING COUNT(a.module_name) = 0
ORDER BY t.time asc

Or, using NOT EXISTS:

WITH times (time) AS (
  SELECT TIMESTAMP '2022-07-06 05:40:00 +02:00'
           + (LEVEL - 1) * INTERVAL '1' MINUTE
  FROM   DUAL
  CONNECT BY 
         TIMESTAMP '2022-07-06 05:40:00 +02:00'
           + (LEVEL - 1) * INTERVAL '1' MINUTE
           < TIMESTAMP '2022-07-06 06:20:00 +02:00'
)
SELECT time
FROM   times t
WHERE  NOT EXISTS(
         SELECT 1
         FROM   "AUDIT" a
         WHERE  t.time <= a.creation_date
         AND    a.creation_date < t.time + INTERVAL '1' MINUTE
         AND    a.MODULE_NAME = 'PERSONAL_PLUGIN'
       )

db<>fiddle here

Upvotes: 1

Related Questions