Mirth message processing time stats, stolen from Chris who borrowed it from Paul

mirth-stats.sql

-- replace d_mmX where X is the local channel ID of the channel you want to check

-- processing time
with started as (select message_id, received_date from d_mm19 where connector_name = 'Source' and status = 'T'),
     max_send_date as (select message_id, max(response_date) as final_send_date
                       from d_mm19
                       where status = 'S'
                         and connector_name != 'Source'
                       group by message_id),
     per_message_start_end as (select s.message_id,
                                      s.received_date,
                                      msd.final_send_date,
                                      (extract('epoch' from msd.final_send_date) -
                                       extract('epoch' from s.received_date)) * 1000 as ms

                               from started s
                                        join max_send_date msd on msd.message_id = s.message_id)
select pmse.message_id, pmse.final_send_date, ms
from per_message_start_end pmse
--         join d_mcm1 on d_mcm1.message_id = pmse.message_id
where date_trunc('day', received_date) = date_trunc('day', final_send_date)
  and date_trunc('day', final_send_date) = '2024-11-21'
order by ms desc
limit 10;

-- daily stats
with started as (select message_id, received_date from d_mm19 where connector_name = 'Source' and status = 'T'),
     max_send_date as (select message_id, max(response_date) as final_send_date
                       from d_mm19
                       where status = 'S'
                         and connector_name != 'Source'
                       group by message_id),
     per_message_start_end as (select s.message_id,
                                      s.received_date,
                                      msd.final_send_date,
                                      (extract('epoch' from msd.final_send_date) -
                                       extract('epoch' from s.received_date)) * 1000 as ms

                               from started s
                                        join max_send_date msd on msd.message_id = s.message_id)
select ROUND(MIN(ms)::numeric, 2) AS min_ms, ROUND(MAX(ms)::numeric, 2) AS max_ms, ROUND(AVG(ms)::numeric, 2) AS average_ms, ROUND(STDDEV_POP(ms)::numeric, 2) AS stddev_ms
from per_message_start_end pmse
--         join d_mcm1 on d_mcm1.message_id = pmse.message_id
where date_trunc('day', received_date) = date_trunc('day', final_send_date)
  and date_trunc('day', final_send_date) = '2024-11-21'