如何在C#/Entity Framework/MSSQL中按指定周时段（支持时区）查询数据？

The core challenge here is converting the UTC-stored datetime values to your target time zone before checking the day of week and time ranges. Here's how to implement this properly:

Step 1: Updated MSSQL Query with Time Zone Handling #

This query uses SQL Server's AT TIME ZONE (available in 2016+) to convert UTC times to your desired time zone, then filters based on local day and time. We'll use ISO weekday numbers (1=Monday, 7=Sunday) for language-agnostic day checks:

DECLARE @Id INT = 123;
DECLARE @TimeZone NVARCHAR(100) = 'Eastern Standard Time'; -- Replace with your target time zone
DECLARE @UtcStartDate DATETIME = '2024-01-01 00:00:00';
DECLARE @UtcEndDate DATETIME = '2024-01-31 23:59:59';

-- Time ranges in target time zone (local time)
DECLARE @MondayStart TIME = '10:00:00';
DECLARE @MondayEnd TIME = '16:00:00';
DECLARE @TuesdayStart TIME = '03:00:00';
DECLARE @TuesdayEnd TIME = '18:00:00';
DECLARE @WednesdayStart TIME = '15:00:00';
DECLARE @WednesdayEnd TIME = '18:00:00';
DECLARE @WeekendStart TIME = '13:00:00';
DECLARE @WeekendEnd TIME = '19:00:00';

WITH LocalizedRecords AS (
    SELECT 
        success,
        -- Convert UTC time to local datetime in target zone
        CAST(time AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone AS DATETIME) AS local_dt,
        CAST(time AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone AS TIME) AS local_time
    FROM recordings
    WHERE Id = @Id 
        AND time >= @UtcStartDate 
        AND time <= @UtcEndDate
)
SELECT 
    CAST(AVG(CAST(success AS FLOAT)*100) AS DECIMAL(18,2)) AS Avg,
    CONVERT(DATE, local_dt) AS [Time]
FROM LocalizedRecords
WHERE 
    (DATEPART(ISO_WEEKDAY, local_dt) = 1 AND local_time BETWEEN @MondayStart AND @MondayEnd)
    OR (DATEPART(ISO_WEEKDAY, local_dt) = 2 AND local_time BETWEEN @TuesdayStart AND @TuesdayEnd)
    OR (DATEPART(ISO_WEEKDAY, local_dt) = 3 AND local_time BETWEEN @WednesdayStart AND @WednesdayEnd)
    OR (DATEPART(ISO_WEEKDAY, local_dt) BETWEEN 4 AND 7 AND local_time BETWEEN @WeekendStart AND @WeekendEnd)
GROUP BY CONVERT(DATE, local_dt)
ORDER BY CONVERT(DATE, local_dt);

Step 2: Entity Framework Core Implementation #

In C#, we'll use EF Core's built-in functions to translate the time zone conversion and filtering to SQL. This ensures the query runs efficiently on the database:

First, define a filter model to hold your parameters: #

public class TimeRangeFilter
{
    public int Id { get; set; }
    public string TimeZoneId { get; set; } // e.g., "Eastern Standard Time" or "Asia/Shanghai"
    public DateTime UtcStartDate { get; set; }
    public DateTime UtcEndDate { get; set; }
    public TimeSpan MondayStart { get; set; }
    public TimeSpan MondayEnd { get; set; }
    public TimeSpan TuesdayStart { get; set; }
    public TimeSpan TuesdayEnd { get; set; }
    public TimeSpan WednesdayStart { get; set; }
    public TimeSpan WednesdayEnd { get; set; }
    public TimeSpan WeekendStart { get; set; }
    public TimeSpan WeekendEnd { get; set; }
}

Then, build the EF Core query: #

using Microsoft.EntityFrameworkCore;

// Populate your filter with user-defined values
var filter = new TimeRangeFilter
{
    Id = 123,
    TimeZoneId = "Eastern Standard Time",
    UtcStartDate = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc),
    UtcEndDate = new DateTime(2024, 1, 31, 23, 59, 59, DateTimeKind.Utc),
    MondayStart = TimeSpan.FromHours(10),
    MondayEnd = TimeSpan.FromHours(16),
    TuesdayStart = TimeSpan.FromHours(3),
    TuesdayEnd = TimeSpan.FromHours(18),
    WednesdayStart = TimeSpan.FromHours(15),
    WednesdayEnd = TimeSpan.FromHours(18),
    WeekendStart = TimeSpan.FromHours(13),
    WeekendEnd = TimeSpan.FromHours(19)
};

var results = await dbContext.Recordings
    .Where(r => r.Id == filter.Id 
                && r.Time >= filter.UtcStartDate 
                && r.Time <= filter.UtcEndDate)
    .Select(r => new
    {
        r.Success,
        // Convert UTC to target time zone local datetime
        LocalDateTime = EF.Functions.AtTimeZone(r.Time, "UTC").AtTimeZone(filter.TimeZoneId).DateTime,
        LocalTime = EF.Functions.AtTimeZone(r.Time, "UTC").AtTimeZone(filter.TimeZoneId).TimeOfDay
    })
    .Where(x => 
        // Monday (ISO weekday 1)
        (EF.Functions.DatePart("iso_weekday", x.LocalDateTime) == 1 
         && x.LocalTime >= filter.MondayStart 
         && x.LocalTime <= filter.MondayEnd)
        || 
        // Tuesday (ISO weekday 2)
        (EF.Functions.DatePart("iso_weekday", x.LocalDateTime) == 2 
         && x.LocalTime >= filter.TuesdayStart 
         && x.LocalTime <= filter.TuesdayEnd)
        || 
        // Wednesday (ISO weekday3)
        (EF.Functions.DatePart("iso_weekday", x.LocalDateTime) == 3 
         && x.LocalTime >= filter.WednesdayStart 
         && x.LocalTime <= filter.WednesdayEnd)
        || 
        // Thursday-Sunday (ISO weekdays 4-7)
        (EF.Functions.DatePart("iso_weekday", x.LocalDateTime) >= 4 
         && EF.Functions.DatePart("iso_weekday", x.LocalDateTime) <=7 
         && x.LocalTime >= filter.WeekendStart 
         && x.LocalTime <= filter.WeekendEnd))
    .GroupBy(x => x.LocalDateTime.Date)
    .Select(g => new
    {
        Date = g.Key,
        AverageSuccess = Math.Round(g.Average(x => x.Success ? 100.0 : 0.0), 2)
    })
    .OrderBy(g => g.Date)
    .ToListAsync();

Key Considerations #

• SQL Server Version: AT TIME ZONE requires SQL Server 2016+, Azure SQL Database, or later.
• Time Zone IDs: Use Windows time zone IDs (e.g., "Central European Standard Time") on Windows servers. For Linux/macOS SQL Server 2019+, you can use IANA IDs (e.g., "Europe/Berlin").
• Performance: Using a CTE (in SQL) or a single projection (in EF) avoids redundant time zone conversions. For large datasets, ensure you have an index on the time column to speed up the initial UTC date filter.
• Language Independence: Using DATEPART(ISO_WEEKDAY) ensures day checks work regardless of SQL Server's language settings (1=Monday, 7=Sunday).
• EF Core Compatibility: EF.Functions.AtTimeZone is available in EF Core 3.0+. For older versions, use raw SQL queries with parameters.

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