Can Azure SQL Preserve Original Time Zone for Auditing?

Goal: Preserve Regional Date Context in Azure SQL

The application needs to record user‑entered dates (e.g., scheduled events, audit timestamps) and later display them in the user’s local time zone. The only supported conversion function is AT TIME ZONE, which relies on the server’s UTC clock and the Windows time zone names exposed by sys.time_zone_info.

Constraints

• Azure SQL Database server time is fixed to UTC; GETDATE() cannot be trusted for local wall‑clock values.
• Converting a datetime (no offset) with AT TIME ZONE assumes the value is already in the target zone, potentially corrupting data.
• Storing datetimeoffset preserves the offset but not the original zone name, so DST rules cannot be re‑derived later.
• Windows zone names differ from IANA names; a mapping layer is required.

Unresolved Decision

Should the system store all timestamps as UTC and perform zone conversion only at read time, or should it persist datetimeoffset values (or the zone name alongside) to retain the user’s original wall‑clock context for auditing and recurring‑schedule scenarios?

Key questions:

1. Does AT TIME ZONE preserve the original wall‑clock context when converting a datetimeoffset value?
2. What is the best strategy for mapping Windows time zone names to IANA names in application code?
3. Which storage model (UTC only vs. datetimeoffset with zone name) provides the most reliable audit trail across DST transitions?