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: these functions are available on current versions of Quantivly. If a query fails with a function ... does not exist error, it may use an older function name; see Update Your Queries.

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 default null,
    _annotations_exclude jsonb default null,
    _ignore_operating_hours bool default false,
    _delay_threshold_minutes int default 60
)

_time_from and _time_to are timestamp values (without time zone). Use now()::timestamp rather than now(); passing now() directly fails with a function ... does not exist error.

A second form of each in-time function replaces the four optional parameters after _change_patient_buffer_minutes with a single _extra argument: (..., _change_patient_buffer_minutes, _ignore_operating_hours bool default false, _extra jsonb default null). _extra accepts keys such as annotations_restrict_to, annotations_exclude, delay_threshold_minutes and extend_range. The examples in this article use the first form.

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)

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 snap 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. With the _extra form of the function, you can pass '{"extend_range": false}' to use your dates exactly as given.
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>.

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`

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	timestamp	-	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	float	fraction	Average scanner utilization (use_time/available_time), taking into account the patient buffer.
schedule_utilization_avg	float	fraction	Average Schedule utilization (booked/available).
exam_daily_cnt_per_hour_avg	float	per hr	Average exams per hour.
appt_daily_cnt_per_hour_avg	float	per hr	Average appointments per hour.
exam_gap_avg	interval	duration	Mean gap between consecutive exams.
appt_gap_avg	interval	duration	Mean gap between consecutive appointments.
exam_gap_daily_max_avg	interval	duration	Mean of each day’s max exam gap, averaged over the bucket.
appt_gap_daily_max_avg	interval	duration	Mean of each day’s max appointment gap, averaged over the bucket.
exam_duration_avg	interval	duration	Average Exam duration.
exam_duration_median	interval	duration	Median Exam duration.
appt_duration_avg	interval	duration	Average appointment duration.
appt_duration_median	interval	duration	Median appointment duration.
appt_exam_start_delay_avg	interval	duration	Average (exam_start − appt_start); positive=late start.
appt_exam_start_delay_median	interval	duration	Median start delay.
appt_exam_end_overrun_avg	interval	duration	Average (exam_end − appt_end); positive=overrun.
appt_exam_end_overrun_median	interval	duration	Median end overrun.
appt_exam_delayed_cnt	int	count	Appointments with start delay > threshold.
appt_exam_delayed_pct	float	fraction	Share of delayed appointments.
appt_exam_checkin_wait_time_avg	interval	duration	Average time from check-in to exam start.
appt_exam_slot_utilization_avg	float	fraction	Average share of appointment slot used by the exam.
scanner_start_finish_interval_avg	interval	duration	Average span from first exam start to last exam end.
schedule_start_finish_interval_avg	interval	duration	Average span from first booked slot start to last booked slot end.
scanner_open_to_start_delay_avg	interval	duration	Average time from scanner open to first exam start.
scanner_close_to_end_overrun_avg	interval	duration	Average time beyond scanner close to last exam end.
schedule_open_to_start_delay_avg	interval	duration	Average time from schedule open to first booked slot start.
schedule_close_to_end_overrun_avg	interval	duration	Average time beyond schedule close to last booked slot end.
schedule_operating_time_sum	interval	duration	Sum of scheduled operating time in the bucket.
schedule_use_time_sum	interval	duration	Sum of booked slot durations (used time).
scanner_use_time_sum	interval	duration	Sum of active scanning time (exam durations).
schedule_overbooked_utilization_avg	float	fraction	Average share of operating time that is overbooked (two or more appointments booked at once). Added to `schedule_utilization_avg`, it gives total booked utilization, which can exceed 1.
schedule_overbooked_time_sum	interval	duration	Sum of overbooked (overlapping) appointment time in the bucket.

Notes:

Timestamps are in the specified _time_zone.
Fractions are 0–1 (×100 for %).
Durations, gaps and delays are returned as PostgreSQL interval values (for example 00:42:30). To get minutes, use extract(epoch from exam_duration_avg) / 60.

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 two new columns, imaging_resource_id and 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, section, or protocol, 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.
`metrics.get_intime_metrics_by_protocol`	`protocol_name`	Compare across protocols. An optional `_protocol_algo_id` parameter picks the protocol naming strategy (default `quantivly.protocol.rule-based.keep-laterality`).

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()::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, 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()::timestamp - interval '8 weeks',
  _time_to                        => now()::timestamp,
  _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;