[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.