夏令时与标准时切换下应用访问时间管控的优化方案咨询

当前方案的核心问题是将本地业务时间固定为某一个UTC时间值，但夏令时切换时，本地时间与UTC的偏移量会变化，导致原来的UTC值对应的本地时间偏离预期（比如EST的8点对应UTC13点，EDT的8点对应UTC12点，固定存储UTC13点的话，EDT时期会对应本地9点）。以下是更优的解决思路：

方案1：修改存储结构，直接存储本地业务时间（推荐） #

调整表结构 #

把原来存储UTC时间模式的字段，改为存储本地时间的时间部分，同时增加时区标识字段：

ALTER TABLE HoursOfBusiness
ADD 
    TimeZoneName NVARCHAR(100) NOT NULL, -- 例如 'Eastern Standard Time' / 'Central Standard Time'
    LoginStartTimeLocal TIME NULL,
    WorkStartTimeLocal TIME NULL,
    LoginEndTimeLocal TIME NULL,
    WorkEndTimeLocal TIME NULL;

-- 迁移数据：将原UTC模式时间转换为对应时区的本地时间
UPDATE HoursOfBusiness
SET
    LoginStartTimeLocal = CAST(LoginStartTimeUTC AT TIME ZONE 'UTC' AT TIME ZONE TimeZoneName AS TIME),
    WorkStartTimeLocal = CAST(WorkStartTimeUTC AT TIME ZONE 'UTC' AT TIME ZONE TimeZoneName AS TIME),
    LoginEndTimeLocal = CAST(LoginEndTimeUTC AT TIME ZONE 'UTC' AT TIME ZONE TimeZoneName AS TIME),
    WorkEndTimeLocal = CAST(WorkEndTimeUTC AT TIME ZONE 'UTC' AT TIME ZONE TimeZoneName AS TIME);

-- 可选：删除原UTC字段
ALTER TABLE HoursOfBusiness
DROP COLUMN LoginStartTimeUTC, WorkStartTimeUTC, LoginEndTimeUTC, WorkEndTimeUTC;

查询逻辑（自动处理夏令时） #

利用SQL Server的AT TIME ZONE函数，自动根据当前日期判断夏令时状态，完成本地时间到UTC的转换：

SELECT
    ID,
    -- 构造当日本地日期时间 → 转换为对应时区 → 转UTC
    CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + LoginStartTimeLocal 
        AT TIME ZONE TimeZoneName 
        AT TIME ZONE 'UTC' AS TodaysLoginStartTimeUTC,
    CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + LoginEndTimeLocal 
        AT TIME ZONE TimeZoneName 
        AT TIME ZONE 'UTC' AS TodaysLoginEndTimeUTC,
    CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + WorkStartTimeLocal 
        AT TIME ZONE TimeZoneName 
        AT TIME ZONE 'UTC' AS TodaysWorkStartTimeUTC,
    CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + WorkEndTimeLocal 
        AT TIME ZONE TimeZoneName 
        AT TIME ZONE 'UTC' AS TodaysWorkEndTimeUTC,
    -- 判断当前是否在工作时段内
    CAST(CASE 
        WHEN GETUTCDATE() BETWEEN 
            (CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + WorkStartTimeLocal AT TIME ZONE TimeZoneName AT TIME ZONE 'UTC')
            AND
            (CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + WorkEndTimeLocal AT TIME ZONE TimeZoneName AT TIME ZONE 'UTC')
        THEN 1 ELSE 0 
    END AS BIT) AS CurrentlyInsideWorkHours,
    -- 判断当前是否在登录时段内
    CAST(CASE 
        WHEN GETUTCDATE() BETWEEN 
            (CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + LoginStartTimeLocal AT TIME ZONE TimeZoneName AT TIME ZONE 'UTC')
            AND
            (CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + LoginEndTimeLocal AT TIME ZONE TimeZoneName AT TIME ZONE 'UTC')
        THEN 1 ELSE 0 
    END AS BIT) AS CurrentlyInsideLoginHours
FROM HoursOfBusiness;

方案2：不修改原表结构，动态转换本地时间 #

如果无法调整表结构，可通过原UTC模式时间反推出本地业务时间，再结合当前日期转换为UTC：

-- 假设表已新增TimeZoneName字段
SELECT
    ID,
    -- 步骤1：将存储的UTC模式时间转换为对应时区的本地时间（提取时间部分）
    -- 步骤2：结合当前日期构造本地日期时间，再转换为UTC
    CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + CAST(LoginStartTimeUTC AT TIME ZONE 'UTC' AT TIME ZONE TimeZoneName AS TIME)
        AT TIME ZONE TimeZoneName 
        AT TIME ZONE 'UTC' AS TodaysLoginStartTimeUTC,
    CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + CAST(LoginEndTimeUTC AT TIME ZONE 'UTC' AT TIME ZONE TimeZoneName AS TIME)
        AT TIME ZONE TimeZoneName 
        AT TIME ZONE 'UTC' AS TodaysLoginEndTimeUTC,
    -- 状态判断逻辑同方案1
    CAST(CASE 
        WHEN GETUTCDATE() BETWEEN 
            (CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + CAST(WorkStartTimeUTC AT TIME ZONE 'UTC' AT TIME ZONE TimeZoneName AS TIME) AT TIME ZONE TimeZoneName AT TIME ZONE 'UTC')
            AND
            (CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + CAST(WorkEndTimeUTC AT TIME ZONE 'UTC' AT TIME ZONE TimeZoneName AS TIME) AT TIME ZONE TimeZoneName AT TIME ZONE 'UTC')
        THEN 1 ELSE 0 
    END AS BIT) AS CurrentlyInsideWorkHours,
    CAST(CASE 
        WHEN GETUTCDATE() BETWEEN 
            (CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + CAST(LoginStartTimeUTC AT TIME ZONE 'UTC' AT TIME ZONE TimeZoneName AS TIME) AT TIME ZONE TimeZoneName AT TIME ZONE 'UTC')
            AND
            (CAST(CAST(GETDATE() AS DATE) AS DATETIME2) + CAST(LoginEndTimeUTC AT TIME ZONE 'UTC' AT TIME ZONE TimeZoneName AS TIME) AT TIME ZONE TimeZoneName AT TIME ZONE 'UTC')
        THEN 1 ELSE 0 
    END AS BIT) AS CurrentlyInsideLoginHours
FROM HoursOfBusiness;

方案优势 #

• 核心逻辑是固定本地业务时间，动态计算UTC时间，符合业务场景（用户设置的是本地8点登录，夏令时切换不改变这个业务规则）
• AT TIME ZONE函数会自动读取系统时区规则，处理夏令时切换的偏移变化，无需手动判断±1小时
• 避免了原方案中固定UTC值导致的夏令时偏移错误

内容的提问来源于stack exchange，提问作者ClearlyClueless