In-Time Metrics

Purpose & Requirements

A common need is to evaluate the evolution of metrics over time (per day, week, or month). We call these in-time metrics.

Requirements : platform version 2.5.0

Function

Function signature

function metrics.get_intime_metrics (
    _imaging_resource_ids int[],
    _time_from timestamp,
    _time_to timestamp,
    _time_zone text,
    _grouping_time_pd text,
    _include_weekdays int[],
    _change_patient_buffer_minutes float,
    _annotations_restrict_to jsonb,
    _annotations_exclude jsonb,
    _ignore_operating_hours bool default false,
    _delay_threshold_minutes int default 60
)

Parameters

Parameter	Type	Req?	Description	Example
`_imaging_resource_ids`	`int[]`	✅	List of imaging resource IDs to include.	`{1,2,3}`
`_time_from`	`timestamp`	✅	Start of the time window (inclusive).	`'2024-08-08T00:00:00'`
`_time_to`	`timestamp`	✅	End of the time window (inclusive).	`'2024-09-10T23:59:59'`
`_time_zone`	`text`	✅	Time zone for interpreting timestamps.	`'America/New_York'`
`_grouping_time_pd`	`text`	✅	Time grouping unit. Common values: `'day'`, `'week'`, `'month'`.	`'day'`
`_include_weekdays`	`int[]`	✅	Days of the week to include (`1=Monday … 7=Sunday`).	`{1,2,3,4,5}` for Monday through Friday
`_change_patient_buffer_minutes`	`float`	⚙️	Buffer (minutes) applied when switching patients to avoid inflating utilization.	`5`
`_annotations_restrict_to`	`jsonb`	⚙️	Restrict metrics to items with specific annotations.	`null`
`_annotations_exclude`	`jsonb`	⚙️	Exclude items with specific annotations.	`null`
`_ignore_operating_hours`	`bool`	⚙️	Ignore configured operating hours if `true`. Default: `false`.	`false`
`_delay_threshold_minutes`	`int`	⚙️	Threshold for considering an appointment/exam as delayed. Default: `60`.	`60`

💡 Legend: ✅ Required · ⚙️ Optional (has default or can be null) \EndCondensedTable

Common time zones include:

U.S.: America/New_York (ET), America/Chicago (CT), America/Los_Angeles (PT)
Europe: Europe/London (UK), Europe/Berlin (CET), Europe/Paris (CET)

Example: daily metrics for all imaging resources

Try this readable, named-argument call (easier to maintain than remembering the position of all arguments):

select *
from metrics.get_intime_metrics(
  -- all scanners
  _imaging_resource_ids           => (select array_agg(resource_id) from imaging_resource),
  _time_from                      => now()::timestamp - interval '2 months', -- last 2 months
  _time_to                        => now()::timestamp,
  _time_zone                      => 'America/New_York',          -- output time zone
  _grouping_time_pd               => 'day',                       -- per-day buckets
  _include_weekdays               => array[1,2,3,4,5,6,7],        -- mon..sun
  _change_patient_buffer_minutes  => 5,                           -- 5 min pre/post exam 
  _ignore_operating_hours         => false
)
order by marker_date;

👉 You’ll get a wide result—each column is a different metric. In the next section, we’ll decode these column names so you know exactly what each represents.

In the meantime, try variations: change 'day' → 'week', restrict weekdays to Monday–Friday with array[1,2,3,4,5], or try using _ignore_operating_hours=true.

⚠️ Important

The in-time functions call an internal helper, metrics.extend_timestamp_range, that snaps your _time_from / _time_to to full calendar buckets (in your _time_zone):
- 'day' → start/end of the day
- 'week' → start/end of the calendar week
- 'month' → first/last day of the month
This prevents biased results caused by partial first/last buckets, keeps denominators consistent for rates/percentages, and handles DST boundaries cleanly. But be aware that the actual dates may not be exactly what you used as input.
The most recent bucket may be partial (e.g., “today”, “this week”). Check period_is_incomplete = true and filter it out for finalized reporting, e.g. with where period_is_incomplete = false.

Column output

Demystifying column names

Metric column names follow a consistent taxonomy and are built using the following pattern: <scope>_<attribute>[_<agg1>]_<agg2>.

\BeginCondensedTable

Token	Purpose	Examples
`scope` (required)	What the metric is about	`exam`, `appt`, `appt_exam` (relationship), `scanner`, `schedule`
`attribute` (required)	What we measure, in snake-case	`duration`, `gap`, `start_delay`, `slot_utilization`
`agg1` (optional)	An optional inner (usually daily) aggregate	`dailyavg`, `dailymax`, `daily_cnt_per_hour`
`agg2` (required)	The final aggregate over the bucket	`avg`, `median`, `max`, `min`, `sum`, `pct`, `cnt`

\EndCondensedTable

Examples (how names map to the pattern):

exam_duration_median → exam • duration • median
appt_exam_delayed_pct → appt_exam • delayed • pct (percentage of delayed appts)
exam_gap_daily_max_avg → exam • gap • dailymax • avg (avg of each day’s max gap)
exam_daily_cnt_per_hour_avg → exam • cnt_per_hour • daily • avg
scanner_utilization_avg → scanner • utilization • avg

The full list of columns returned by metrics.get_intime_metrics is described below:

Column	Type	Units	Meaning
marker_date	date	-	Label for the period (start-of-bucket).
period_is_incomplete	bool	-	True if the bucket isn’t fully elapsed (e.g., “today” still in progress).
interval_start	timestamp	-	Start timestamp of the bucket.
interval_end	timestamp	-	End timestamp of the bucket.
exam_start_min	timestamp	-	Earliest exam start in the bucket.
exam_end_max	timestamp	-	Latest exam end in the bucket.
appt_start_min	timestamp	-	Earliest appointment start in the bucket.
appt_end_max	timestamp	-	Latest appointment end in the bucket.
exam_accnumber_cnt	int	count	Exam count, calculated as the number of distinct exam accession numbers.
exam_block_cnt	int	count	Exam count, calculated as the number of contiguous collections of acquisitions ("blocks").
exam_study_cnt	int	count	Exam count, calculated as the number of distinct study instance UIDs.
appt_accnumber_cnt	int	count	Appointment count, calculated as the number of distinct scheduled accession numbers.
appt_block_cnt	int	count	Appointment count, calculated as the number of appointment blocks scheduled.
appt_order_cnt	int	count	Appointment count, calculated as the number of unique RIS orders linked to appointments.
scanner_utilization_avg	numeric	fraction	Average scanner utilization (use_time/available_time), taking into account the patient buffer.
schedule_utilization_avg	numeric	fraction	Average Schedule utilization (booked/available).
exam_daily_cnt_per_hour_avg	numeric	per hr	Average exams per hour.
appt_daily_cnt_per_hour_avg	numeric	per hr	Average appointments per hour.
exam_gap_avg	numeric	min	Mean gap between consecutive exams.
appt_gap_avg	numeric	min	Mean gap between consecutive appointments.
exam_gap_daily_max_avg	numeric	min	Mean of each day’s max exam gap, averaged over the bucket.
appt_gap_daily_max_avg	numeric	min	Mean of each day’s max appointment gap, averaged over the bucket.
exam_duration_avg	numeric	min	Average Exam duration.
exam_duration_median	numeric	min	Median Exam duration.
appt_duration_avg	numeric	min	Average appointment duration.
appt_duration_median	numeric	min	Median appointment duration.
appt_exam_start_delay_avg	numeric	min	Average (exam_start − appt_start); positive=late start.
appt_exam_start_delay_median	numeric	min	Median start delay.
appt_exam_end_overrun_avg	numeric	min	Average (exam_end − appt_end); positive=overrun.
appt_exam_end_overrun_median	numeric	min	Median end overrun.
appt_exam_delayed_cnt	int	count	Appointments with start delay > threshold.
appt_exam_delayed_pct	numeric	fraction	Share of delayed appointments.
appt_exam_checkin_wait_time_avg	numeric	min	Average time from check-in to exam start.
appt_exam_slot_utilization_avg	numeric	fraction	Average share of appointment slot used by the exam.
scanner_start_finish_interval	numeric	min	First exam start → last exam end span.
schedule_start_finish_interval	numeric	min	First booked slot start → last booked slot end span.
scanner_open_to_start_delay_avg	numeric	min	Average time from scanner open to first exam start.
scanner_close_to_end_overrun_avg	numeric	min	Average time beyond scanner close to last exam end.
schedule_open_to_start_delay_avg	numeric	min	Average time from schedule open to first booked slot start.
schedule_close_to_end_overrun_avg	numeric	min	Average time beyond schedule close to last booked slot end.
schedule_operating_time_sum	numeric	min	Sum of scheduled operating time in the bucket.
schedule_use_time_sum	numeric	min	Sum of booked slot durations (used time).
scanner_use_time_sum	numeric	min	Sum of active scanning time (exam durations).

Notes:

Timestamps are in the specified _time_zone.
Fractions are 0–1 (×100 for %).
Durations/intervals are minutes unless noted.

Selecting only what you need

For dashboards or reports, you often don’t need all columns. You can select just a few columns using for example:

select
  marker_date,
  appt_accnumber_cnt,
  exam_accnumber_cnt,
  schedule_utilization_avg,
  scanner_utilization_avg
from metrics.get_intime_metrics(
  _imaging_resource_ids => (select array_agg(resource_id) from imaging_resource),
  _time_from             => now()::timestamp - interval '2 months',     -- last two months
  _time_to               => now()::timestamp,
  _time_zone             => 'America/New_York',
  _grouping_time_pd      => 'day',
  _include_weekdays      => array[1,2,3,4,5,6,7],
  _change_patient_buffer_minutes => 5,
  _ignore_operating_hours  => false
)
order by marker_date;

This produces one row per day with key metrics:

Number of scheduled appointments (unique accession numbers scheduled, appt_accnumber_cnt)
Number of completed exams (unique accession numbers performed, exam_accnumber_cnt)
Average schedule utilization (schedule_utilization_avg)
Average scanner utilization (scanner_utilization_avg)

Variants

Variants by scanner

Sometimes you need to compare machines—spot under/over-utilized scanners, verify balancing across sites, or investigate delays tied to a specific unit. For that, use the per-device (_by_scanner) variant:

select 
  marker_date,
  station_name,
  appt_accnumber_cnt,
  exam_accnumber_cnt,
  schedule_utilization_avg,
  scanner_utilization_avg
from metrics.get_intime_metrics_by_scanner(
  _imaging_resource_ids           => (select array_agg(resource_id) from imaging_resource),
  _time_from                      => now()::timestamp - interval '2 months',
  _time_to                        => now()::timestamp,
  _time_zone                      => 'America/New_York',
  _grouping_time_pd               => 'day',
  _include_weekdays               => array[1,2,3,4,5,6,7],
  _change_patient_buffer_minutes  => 5,
  _ignore_operating_hours         => false
)
order by marker_date, station_name;

This returns one row per day per scanner, with a new column station_name, so you can compare activity and utilization across machines.

💡 Tip: If the last bucket includes “today,” check period_is_incomplete before drawing conclusions.

Example : Top 5 under-utilized MR scanners in the last 30 days

You can tweak the definition of _imaging_resource_ids to include only MR scanners, and then aggregate the results to identify the top 5 under-utilized MR devices.

For example:

select 
  station_name,
  avg(scanner_utilization_avg) as util_30d_avg
from metrics.get_intime_metrics_by_scanner(
  _imaging_resource_ids           => (select array_agg(resource_id) from imaging_resource inner join imaging_resource_modality using (imaging_resource_id) where modality='MR'),
  _time_from                      => now()::timestamp - interval '30 days',
  _time_to                        => now()::timestamp,
  _time_zone                      => 'America/New_York',
  _grouping_time_pd               => 'day',
  _include_weekdays               => array[1,2,3,4,5,6,7],
  _change_patient_buffer_minutes  => 5,
  _ignore_operating_hours         => false
)
group by station_name
order by util_30d_avg asc
limit 5;

Other in-time metric variants

If you want to break results out by patient class, anesthesia status, or section, you can use the following variants:

function name	Adds column	When to use
`metrics.get_intime_metrics_by_patient_class`	`patient_class`	Compare inpatients vs. outpatients (and other site-specific classes).
`metrics.get_intime_metrics_by_sedation`	`anesthesia_type`	Compare metrics with/without anesthesia or by anesthesia type.
`metrics.get_intime_metrics_by_section`	`section_name`	Compare across sections defined at your site.

Each returns the same columns as get_intime_metrics(...), plus one extra dimension column, and produce one row per time bucket per value of that dimension. Note that values can be site-specific.

Example: by patient class (daily, last 2 months)

select
  marker_date,
  patient_class,
  exam_accnumber_cnt,
  appt_accnumber_cnt,
  scanner_utilization_avg,
  schedule_utilization_avg
from metrics.get_intime_metrics_by_patient_class(
  _imaging_resource_ids           => (select array_agg(resource_id) from imaging_resource),
  _time_from                      => now() - interval '2 months',
  _time_to                        => now(),
  _time_zone                      => 'America/New_York',
  _grouping_time_pd               => 'day',
  _include_weekdays               => array[1,2,3,4,5,6,7],
  _change_patient_buffer_minutes  => 5,
  _ignore_operating_hours         => false
)
order by marker_date, patient_class;

Example: by anesthesia type (weekdays only)

select
  marker_date,
  anesthesia_type,
  appt_exam_delayed_pct,
  appt_exam_start_delay_median,
  exam_duration_median
from metrics.get_intime_metrics_by_sedation(
  _imaging_resource_ids           => (select array_agg(resource_id) from imaging_resource),
  _time_from                      => now() - interval '8 weeks',
  _time_to                        => now(),
  _time_zone                      => 'America/New_York',
  _grouping_time_pd               => 'week',
  _include_weekdays               => array[1,2,3,4,5], -- Mon–Fri
  _change_patient_buffer_minutes  => 5,
  _annotations_restrict_to        => null,
  _annotations_exclude            => null,
  _ignore_operating_hours         => false,
  _delay_threshold_minutes        => 60
)
order by marker_date, anesthesia_type;