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.

  1. 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 the TB_MESSAGE_LOG table where the message handling role is SENDING, and the received timestamp falls within the specified interval.

  2. 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 the SENDING role and the specific time frame, additionally ensuring that the message status is ACKNOWLEDGED.

  3. 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 is RECEIVING, fitting within your specified time range, and with the status RECEIVED.

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.

  • No labels