To present the following queries, let's use the time interval from Tue Mar 02 15:20:12 CET 2021 to Tue Mar 02 16:28:12 CET 2021.
How can I determine the number of messages received by C2 within a specific time interval using Oracle?
To find out the number of messages received by C2 between March 2nd, 2021, from 15:20:12 CET to 16:28:12 CET, you can use the following SQL query:SELECT
count
(*)
FROM
TB_MESSAGE_LOG
WHERE
MSH_ROLE =
'SENDING'
AND
RECEIVED
BETWEEN
'02/03/21 15:20:12,000000000'
AND
'02/03/21 16:28:12,000000000'
;
This query counts all entries in theTB_MESSAGE_LOG
table where the message handling role isSENDING
, and the received timestamp falls within the specified interval.How can I check the number of messages sent by C2 within a certain timeframe?
To determine the count of messages sent by C2 between 15:20:12 and 16:28:12 on March 2nd, 2021, you would execute this query:SELECT
count
(*)
FROM
TB_MESSAGE_LOG
WHERE
MSH_ROLE =
'SENDING'
AND
RECEIVED
BETWEEN
'02/03/21 15:20:12,000000000'
AND
'02/03/21 16:28:12,000000000'
AND
MESSAGE_STATUS =
'ACKNOWLEDGED'
;
This query filters the records by theSENDING
role and the specific time frame, additionally ensuring that the message status isACKNOWLEDGED
.How do I find out the number of messages received by C3 in a specific period?
To ascertain the number of messages received by C3 from 15:20:12 to 16:28:12 on the 2nd of March, 2021, use this SQL command:SELECT
count
(*)
FROM
TB_MESSAGE_LOG
WHERE
MSH_ROLE =
'RECEIVING'
AND
RECEIVED
BETWEEN
'02/03/21 15:20:12,000000000'
AND
'02/03/21 16:28:12,000000000'
AND
MESSAGE_STATUS =
'RECEIVED'
;
In this query, we are counting messages where the role isRECEIVING
, fitting within your specified time range, and with the statusRECEIVED
.
Note: Modify the MESSAGE_STATUS
to either DOWNLOADED
or DELETED
in the queries above, based on the retention policy set in your PMode. This adjustment ensures alignment with your specific data management policies.