Inbound report time is wrong

Source and affected scope

MariaDB CONVERT_TZ ↗

Source date

Not stated in source

Source checked

2026-11-07

Reported version

Version-specific behavior; inspect the installed release

Guide updated

2026-11-07

• Inbound report timestamps differ from an expected local display.

Before you start

• Read-only SQL access to an authorized test dataset.
• Know the report filter, server timezone, database session timezone and the authoritative timezone of the original event.

Diagnose the reported problem

1. Step 1Record the time contexts

   On the database host record the system timezone; in the same reporting session query database and session timezone. Do not assume a DATETIME column stores UTC.

   Inspect the report’s date filter and the exact timestamp column and transformation in its source or documented output.

   Before you use it

   Run only on an authorized isolated lab or approved read-only diagnostic session; substitute placeholders. Preserve sensitive data privately.

   timedatectlsudo mysql --defaults-file=/etc/vicidial-readonly.cnf -e "SELECT CURRENT_USER(), DATABASE(), @@global.time_zone, @@session.time_zone, NOW(), UTC_TIMESTAMP();"

   Reader-run example · not executed for this guide

   This example has no recorded result here. Follow its preparation, expected result and stop conditions before using it.

   Success looks like

   The event source, storage interpretation, database session and report display timezone are each documented.

   Stop if

   If the source timezone is unknown, stop: a numeric offset by itself cannot identify the faulty component.

   Expected observation

   The event source, storage interpretation, database session and report display timezone are each documented.

   If this check fails

   If the source timezone is unknown, stop: a numeric offset by itself cannot identify the faulty component.

2. Step 2Check a synthetic conversion

   For a fictional timestamp explicitly known to be UTC, convert it using a fixed offset; on a date when the relevant locality is UTC-05:00, 15:00 UTC becomes 10:00 local. Fixed offsets are not a daylight-saving rule.

   For real named zones use installed, current timezone data and the applicable historical instant; MariaDB returns NULL when required named-zone tables are unavailable.

   Before you use it

   Run only on an authorized isolated lab or approved read-only diagnostic session; substitute placeholders. Preserve sensitive data privately.

   SELECT CONVERT_TZ('2026-01-15 15:00:00','+00:00','-05:00') AS example_local;

   Reader-run example · not executed for this guide

   This example has no recorded result here. Follow its preparation, expected result and stop conditions before using it.

   Success looks like

   The test demonstrates the arithmetic only; compare the identified actual report transformation separately.

   Stop if

   NULL or an unknown source timezone means do not adjust report or stored rows.

   Expected observation

   The test demonstrates the arithmetic only; compare the identified actual report transformation separately.

   If this check fails

   NULL or an unknown source timezone means do not adjust report or stored rows.

Correct the confirmed cause

Use this branch only when your diagnostic observations match the affected scope above. The lab result states which outcomes were actually tested.

1. Step 3Correct the documented display boundary

   Have the reporting owner correct only the identified report/session timezone setting or conversion after recording its prior value and approving the change. Never rewrite historical call timestamps to hide an offset.

   Expected observation

   The report documents its source and display timezone.

   If this check fails

   If the mismatch changes at daylight-saving transitions, do not hard-code a year-round offset.

Verify the outcome

• Rerun the same authorized synthetic event and report filters, including dates on both sides of a daylight-saving transition; compare to a trusted timezone reference.

Recover or roll back

• Restore the recorded report/session setting and retest the same examples; preserve original database rows.

Screenshot evidence

No native screenshot is attached to this guide.

• MariaDB CONVERT_TZ · Primary documentation; checked 2026-11-07