[FEATURE] Dashboard Time Range should Local and UTC option

[asuhagia]

Problem Statement

If servers are hosted in different time zones (e.g., EST) than the dashboard (e.g., CST), the time range is not populating data appropriately.

Proposed Solution

The collection should save collect_time in UTC, and while showing in the dashboard, it can have an option to visualize based on UTC or the dashboard's local time zone.

Use Case

This will give uniformity even if monitored servers are in different time zones and the centralized dashboard is in a different time zone.

Alternatives Considered

None

Additional Context

• Is this related to a specific SQL Server version? No
• Would this require schema changes? Yes, you can have an additional column, collection_time_utc, to store UTC date, or you can update the logic to change how the timestamp is stored in collection_time.
• How frequently would you use this feature? - daily

Scope

Please indicate which component(s) this affects:

• Full Dashboard
• Lite
• SQL collection scripts
• Installer
• Documentation

IMPORTANT: If you're going to contribute code, please read the contributing guide first.

[erikdarlingdata]

Investigation Results

We audited all query paths in the Dashboard for time zone handling gaps. The core issue is confirmed — when the server and client are in different time zones, custom date range filtering breaks because client-local times are compared against server-local collection_time values.

What's Working

The hours-back path (e.g., "Last 4 Hours" button) is correct everywhere. These queries use DATEADD(HOUR, @hours_back, SYSDATETIME()) entirely on the server side, so both sides of the comparison are in server time. No issue there.

What's Broken

1. DateTime.Now used directly in two places:

• DatabaseService.QueryPerformance.cs — GetQueryStoreRegressionsAsync() falls back to DateTime.Now when no custom dates are provided
• DateFilterHelper.cs — All relative date expressions ("now", "last N hours/days") use DateTime.Now instead of ServerTimeHelper.ServerNow

2. No server time conversion in ~40+ DatabaseService methods:
Every method that accepts fromDate/toDate parameters passes them straight to SQL parameters without calling ServerTimeHelper.ToServerTime(). Affected services:

• DatabaseService.QueryPerformance.cs (16 methods)
• DatabaseService.Memory.cs (6 methods)
• DatabaseService.SystemEvents.cs (12 methods)
• DatabaseService.ResourceMetrics.cs (6 methods)
• DatabaseService.Overview.cs (1 method)

Complication

ServerTab.xaml.cs already calls ServerTimeHelper.ToServerTime() on the global date pickers before passing to these methods. So the fix needs to be consistent — either convert at the caller level (and audit all callers) or convert inside DatabaseService (and remove the existing caller conversion) to avoid double-converting.

Current ServerTimeHelper Infrastructure

The plumbing exists (ServerTimeHelper.ServerNow, ToServerTime(), ToLocalTime(), UtcOffsetMinutes per server) — it's just not used consistently across all code paths.

Next Steps

Plan a consistent fix across all affected paths. The hours-back queries are fine — this only affects custom date range usage.

[erikdarlingdata]

Investigation Notes

Looked into what this would take without any schema changes. The approach: add a display-mode toggle (Server Time / Local Time / UTC) that converts timestamps at the presentation layer only. No query or SQL changes needed — collection_time stays in server time, we just shift for display.

Where timestamps are shown to the user

1. Status text ("Showing: Feb 7, 2:15 PM – 3:15 PM") — currently server time, no timezone label
2. Custom date pickers — already convert server↔local, but hardcoded to client-local
3. Chart X-axis ticks — ScottPlot auto-formats from OADate values, currently in server time
4. Chart hover tooltips — HH:mm:ss from server time
5. DataGrid cells — raw collection_time from the database (server time)

Implementation plan

Add a TimeDisplayMode enum and a central conversion method in ServerTimeHelper:

public static DateTime ConvertForDisplay(DateTime serverTime, TimeDisplayMode mode) => mode switch
{
    TimeDisplayMode.LocalTime => ToLocalTime(serverTime),
    TimeDisplayMode.UTC => serverTime.AddMinutes(-UtcOffsetMinutes),
    _ => serverTime
};

Apply it in:

• FormatDateRange() — convert before formatting, append timezone label like "(EST)" or "(UTC)"
• SetPickersFromDateTime() — use selected display mode instead of hardcoded local
• Chart-building methods — shift OADate arrays before plotting (~30 charts across 4 content controls, but mechanical)
• IValueConverter for DataGrid DateTime column bindings

Scope

Medium effort. ~15 touch points across ~5 files. No schema changes, no SQL changes, no query changes. The chart code is the bulk of it but repetitive.

[erikdarlingdata]

Current State (Feb 2026)

Re-audited all timezone code paths. The functional bugs are fixed — custom date ranges return correct data even when server and client are in different timezones. Here's the full status:

What's Working (no action needed)

Area	Status
DateFilterHelper	Uses ServerTimeHelper.ServerNow everywhere, no DateTime.Now
Date picker conversion	ServerTab converts local→server at lines 800-801 before all DB calls
Chart axis bounds	All ~30 chart methods use ServerTimeHelper.ServerNow as fallback
Chart OADate handling	Correct conversion to/from OADate
GetQueryStoreRegressionsAsync	Fixed — uses ServerTimeHelper.ServerNow for default range
Hours-back queries	Always correct (server-side DATEADD)

What Remains: Display-Only Gaps

All remaining work is presentation layer — the data is correct, users just can't tell what timezone they're looking at or switch it.

1. No TimeDisplayMode toggle — no enum, no UI control, no preference. This is the main feature gap.
2. No timezone labels — FormatDateRange() shows "Feb 7, 2:15 PM – 3:15 PM" with no "(EST)" or "(Server Time)" suffix
3. DataGrid DateTime columns — all display raw server time values without conversion or timezone indicator (affects QueryPerformanceContent, CriticalIssuesContent, CollectionLogWindow, ServerTab)
4. Chart hover tooltips — ChartHoverHelper shows HH:mm:ss with no timezone context
5. One stale DateTime.Now — QueryPerformanceContent.xaml.cs line 582, "Last refreshed" timestamp for the Current Active Queries tab. Cosmetic only (shows when the user clicked refresh, not a DB time), but inconsistent.

Remaining Implementation Plan

Scope: ~15 touch points across ~5 files. No schema or SQL changes.

1. Add TimeDisplayMode enum: ServerTime, LocalTime, UTC
2. Add PreferredTimeDisplayMode to UserPreferences (default: ServerTime for backwards compatibility)
3. Add toggle control to ServerTab toolbar (dropdown or segmented button near the date pickers)
4. Add ServerTimeHelper.ConvertForDisplay(DateTime serverTime, TimeDisplayMode mode) method
5. Update FormatDateRange() to append timezone label based on mode
6. Create IValueConverter for DataGrid DateTime columns that respects the display mode
7. Update chart tooltip formatting in ChartHoverHelper to include timezone
8. Shift chart OADate arrays when display mode is Local or UTC (so X-axis labels match)
9. Update SetPickersFromDateTime() to use selected display mode instead of hardcoded local conversion