Make agent time detail match reality

Confirm what VICIdial already computes before you rebuild it

Fast answer: before reconstructing agent time by hand, check whether NON-AGENT_API.txt's agent_stats_export function already answers the question. For a datetime_start/datetime_end window you choose, it returns pre-computed columns per agent, among them login_time, total_talk_time, avg_talk_time, avg_wait_time, pause_time, wait_time, talk_time, dispo_time and dead_time. If its numbers already match what you expected, there is no report to rebuild; if they do not, the manual work below is how you find out why, not a replacement for the export.

The function documents two more settings worth knowing before you reach for a manual query instead: time_format controls whether the returned durations print as H:MM:SS, minutes, or raw seconds, and group_by_campaign, when set to YES, splits one agent's totals across every campaign they logged into during the window rather than collapsing them into one row. A mismatch between what you expected and what agent_stats_export returned is sometimes nothing more than one of these two settings left at a default that does not match the report you are trying to reproduce.

The raw event source behind any such report is vicidial_agent_log, the same table this library's operator guide already reads for pause_type and pause_sec. This article adds sub_status to that same read. Query it and record whatever values sub_status actually holds on your build before assuming what they mean — the exact semantics vary enough by revision that this article will not assert them for you.

Prefer the export whenever a report only needs the totals it already computes. Reach for the raw table instead only when you need per-event detail the export does not expose at all — the exact sequence and timing of individual pause and status changes within a session, not just their sums.

SHOW COLUMNS FROM vicidial_agent_log; SELECT event_time, sub_status, pause_type, pause_secFROM vicidial_agent_logWHERE user = '<AGENT_USER>'  AND event_time >= CURDATE() - INTERVAL 1 DAYORDER BY event_timeLIMIT 200;

Treat state as intervals, not labels

Fast answer: this is the same clipping rule agent_stats_export already applies internally. Work it by hand only to explain or rebuild a mismatch against that export, not as your primary report. A state change has a start but often no explicit end; derive its end from the next event, then intersect (clip) that interval with the agent's actual login window and report boundary.

An agent who never logs out cleanly — a crashed browser tab, a lost network connection — leaves a final state change with no next event to derive an end from at all. Treat that last open interval as ending at either the report boundary or a documented forced-logout time, whichever is earlier and actually knowable, and say plainly in the report which one you used; silently picking one without disclosing it is how two people reconciling the same day arrive at two different totals.

Rows outside the window must not become visible simply because their state label matches the current filter.

See vicidial-terminology-for-complete-beginners for the rest of this glossary.

visible_start = max(event_start, login_start, report_start)visible_end   = min(next_event_start, logout_time, report_end)visible_secs  = max(0, visible_end - visible_start)

Work one session by hand before writing SQL

A tiny timeline makes the rule visible. In the example, the report starts at 09:00 but login starts at 09:05, so the first five minutes cannot be assigned to the agent; logout at 09:30 clips any later state.

These times are synthetic. Calculate each duration in seconds, then verify the sum equals the 25-minute login window without gaps or overlaps unless the source explicitly documents them.

A second worked case is worth running once you trust the first: two overlapping login windows for the same agent, from a double login the browser tab problem above can produce. The clipping rule handles it the same way it handles the report boundary — max() and min() against both windows at once — but only if your query actually detects the overlap first and treats it as two sessions to reconcile separately, rather than quietly summing both as though they were sequential.

report window: 09:00–10:00login window:  09:05–09:30 READY  raw 09:00–09:12  → visible 09:05–09:12 = 420 secPAUSED raw 09:12–09:20  → visible 09:12–09:20 = 480 secREADY  raw 09:20–09:40  → visible 09:20–09:30 = 600 secvisible total: 1,500 sec = 25 min

Keep visible and hidden evidence separate

Preserve the raw event duration for audit, then calculate a visible duration for presentation. That avoids rewriting history while keeping the report faithful to the user's scope.

Expose why a row is excluded: before login, after logout, hidden by policy or outside the selected date.

• Before login: the raw event's own timestamp predates login_start for this session.
• After logout: the raw event's own timestamp is at or after logout_time.
• Hidden by policy: excluded deliberately by the report's own access rules, not by a timing accident.
• Outside the selected date: falls entirely before report_start or after report_end.

Reconcile daily totals

For a controlled agent day, sum clipped intervals and compare them with login duration, pause detail and call timestamps. Gaps and overlaps should be explainable.

Test midnight boundaries and an agent with multiple login sessions. Those cases reveal most interval mistakes quickly.

Allow a small, named rounding tolerance rather than demanding an exact match to the second — a few seconds of drift between independently computed totals is normal timestamp-precision noise, not evidence of a bug. State the tolerance you are using explicitly, though: an undisclosed tolerance is indistinguishable from a report that simply does not reconcile.

Stop on an unexplained second and reverse the query change

Do not release a revised report while synthetic interval totals exceed the login window, cross session boundaries or differ between repeated runs at the same as-of time. Record the smallest failing fixture and preserve the old query/version.

Rollback means restoring the prior report calculation or view without altering event history. Fix the interval derivation in a test copy, repeat midnight and multi-login cases, then ask an independent reviewer to reproduce the arithmetic.

• Old and proposed query definitions remain versioned and reversible.
• Synthetic normal, midnight and multiple-login sessions reconcile independently.
• Visible and hidden durations remain separate from raw audit evidence.