Temp_MSSPLASHPage/DATETIME_TIMEZONE_FIX_ANALYSIS.md

DateTime Timezone Error - Deep Analysis and Solutions

🎯 Executive Summary

Problem: Top5BranchesMetrics API endpoint fails in SplashPage.Web.Host with error:

Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported.

Root Cause: Missing Npgsql configuration switches in Web.Host that exist in Web.Mvc.

Impact: API endpoints using WiFi scanning data fail in Web.Host but work in Web.Mvc (legacy).

---

🔍 Root Cause Analysis

Difference Between Web.Host and Web.Mvc

✅ Web.Mvc (WORKS)

File: src/SplashPage.Web.Mvc/Startup/Program.cs (Lines 15-16)

public static void Main(string[] args)
{
    // ✅ These switches make Npgsql accept DateTime with Kind=Local
    AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);
    AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

    CreateHostBuilder(args).Build().Run();
}

❌ Web.Host (FAILS)

File: src/SplashPage.Web.Host/Startup/Program.cs

public static void Main(string[] args)
{
    // ❌ MISSING: No Npgsql configuration switches
    CreateHostBuilder(args).Build().Run();
}

---

📊 Technical Deep Dive

How Npgsql Handles DateTimes

Without EnableLegacyTimestampBehavior

• Npgsql strictly requires all DateTime values to have DateTimeKind.Utc
• Rejects DateTime with Kind=Local or Kind=Unspecified
• Throws exception: "Cannot write DateTime with Kind=Local to PostgreSQL..."

With EnableLegacyTimestampBehavior = true

• Npgsql accepts DateTime with any DateTimeKind
• Automatically converts Local/Unspecified to UTC
• Maintains backward compatibility with older code

---

Error Flow in Top5BranchesMetrics

1. Controller: Top5BranchesMetrics(SplashDashboardDto input)
   ↓
2. Service: _metricsQueryService.CalculateBranchMetricsAsync(filter)
   ↓
3. Query Service: var scanningTask = await _scanningService.GetAllEntitiesAsync(scanningFilter)
   Returns: IQueryable<SplashWifiScanningReport> (NOT EXECUTED YET)
   ↓
4. Line 162: var networkScanning = scanning.Where(s => s.NetworkId == networkId).ToList()
   ↓
5. EF Core executes query and materializes entities from database view
   ↓
6. DateTime properties come from view as Kind=Local (view has no timezone info)
   ↓
7. ❌ WITHOUT SWITCH: Npgsql rejects DateTime with Kind=Local
   💥 ERROR THROWN

   ✅ WITH SWITCH: Npgsql accepts and converts automatically
   ✅ SUCCESS

---

Why TopLoyalUsers Works

File: src/SplashPage.Application/Splash/SplashMetricsService.cs (Lines 1365-1368)

public async Task<List<SplashTopLoyalUsersDto>> TopLoyalUsers(SplashDashboardDto input)
{
    var normalizedInput = await NormalizeDashboardInputAsync(input);
    return await _wifiConnectionReportRepo.GetTopLoyalUsersAsync(...);
}

File: src/SplashPage.EntityFrameworkCore/EntityFrameworkCore/Repositories/SplashWifiConnectionReportRepository.cs (Lines 195-218)

public async Task<List<SplashTopLoyalUsersDto>> GetTopLoyalUsersAsync(...)
{
    return await query
        .Where(x => x.UserId > 0)
        .GroupBy(x => new { x.UserId, x.UserName, x.Email })
        .Select(g => new SplashTopLoyalUsersDto
        {
            Name = g.Key.UserName,
            Email = g.Key.Email,
            TotalConnections = g.Count(),
            LastConnection = g.Max(c => c.ConnectionDateTime),  // ✅ Direct projection
            Status = g.OrderByDescending(c => c.ConnectionDateTime).Select(c => c.ConnectionStatus).FirstOrDefault()
        })
        .OrderByDescending(u => u.TotalConnections)
        .Take(topValue)
        .ToListAsync();  // ✅ Executes immediately, no IQueryable leakage
}

Key Difference:

• TopLoyalUsers: Query executes immediately in repository with .ToListAsync()
• Top5BranchesMetrics: Query returns IQueryable, executes later with additional .Where().ToList()

---

Existing DateTime Handling in DbContext

File: src/SplashPage.EntityFrameworkCore/EntityFrameworkCore/SplashPageDbContext.cs (Lines 61-70)

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        foreach (var property in entityType.GetProperties())
        {
            if (property.ClrType == typeof(DateTime) || property.ClrType == typeof(DateTime?))
            {
                // ✅ ValueConverter for entities ONLY
                property.SetValueConverter(new ValueConverter<DateTime, DateTime>(
                    v => v.ToUniversalTime(),                    // Writing: Convert to UTC
                    v => DateTime.SpecifyKind(v, DateTimeKind.Utc) // Reading: Mark as UTC
                ));
            }
        }
    }
    // ...
}

Limitation: This converter only applies to entity classes, NOT to:

• Database views (like SplashWifiScanningReport)
• Raw SQL queries
• Views without entity configuration
• Manual DTO mapping

---

💡 Solution Options

Solution 1: Add Switches to Web.Host (RECOMMENDED - Quick Fix)

Priority: ⭐⭐⭐⭐⭐ (Highest) Effort: ⏱️ 5 minutes Risk: 🟢 Low

Implementation

File: src/SplashPage.Web.Host/Startup/Program.cs

using Abp.AspNetCore.Dependency;
using Abp.Dependency;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Hosting;
using System; // ✅ ADD THIS

namespace SplashPage.Web.Host.Startup
{
    public class Program
    {
        public static void Main(string[] args)
        {
            // ✅ ADD THESE TWO LINES - Same as Web.Mvc
            AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);
            AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

            CreateHostBuilder(args).Build().Run();
        }

        internal static IHostBuilder CreateHostBuilder(string[] args) =>
            Microsoft.Extensions.Hosting.Host.CreateDefaultBuilder(args)
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup<Startup>();
                })
                .UseCastleWindsor(IocManager.Instance.IocContainer);
    }
}

Pros

• ✅ Immediate fix - 2 lines of code
• ✅ Consistent with Web.Mvc configuration
• ✅ No breaking changes to existing code
• ✅ Fixes ALL DateTime issues across the application
• ✅ Zero deployment risk

Cons

• ⚠️ Uses "legacy" mode (may deprecate in future Npgsql versions)
• ⚠️ Not the "cleanest" architectural solution

Testing

# Test the API endpoint
POST https://localhost:44311/api/services/app/SplashMetrics/Top5BranchesMetrics
Content-Type: application/json

{
    "startDate": "2025-10-16T06:00:00.000Z",
    "endDate": "2025-10-23T05:59:59.999Z",
    "selectedNetworks": [82, 93, 95, 372, 17],
    "selectedNetworkGroups": []
}

---

Solution 2: Centralize Configuration in EntityFramework Module (RECOMMENDED - Long Term)

Priority: ⭐⭐⭐⭐ (High) Effort: ⏱️ 15 minutes Risk: 🟡 Medium

Implementation

File: src/SplashPage.EntityFrameworkCore/EntityFrameworkCore/SplashPageEntityFrameworkModule.cs

using Abp.EntityFrameworkCore.Configuration;
using Abp.Modules;
using Abp.Reflection.Extensions;
using Abp.Zero.EntityFrameworkCore;
using SplashPage.EntityFrameworkCore.Seed;
using System;

namespace SplashPage.EntityFrameworkCore;

[DependsOn(
    typeof(SplashPageCoreModule),
    typeof(AbpZeroCoreEntityFrameworkCoreModule))]
public class SplashPageEntityFrameworkModule : AbpModule
{
    public bool SkipDbContextRegistration { get; set; }
    public bool SkipDbSeed { get; set; }

    public override void PreInitialize()
    {
        // ✅ ADD: Global Npgsql configuration BEFORE DbContext registration
        // This ensures consistent DateTime handling across Web.Host and Web.Mvc
        AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
        AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);

        if (!SkipDbContextRegistration)
        {
            Configuration.Modules.AbpEfCore().AddDbContext<SplashPageDbContext>(options =>
            {
                if (options.ExistingConnection != null)
                {
                    var connectionString = Environment.GetEnvironmentVariable(SplashPageConsts.EnvConnectionStringName)
                         ?? options.ConnectionString;

                    SplashPageDbContextConfigurer.Configure(options.DbContextOptions, connectionString);
                }
                else
                {
                    var connectionString = Environment.GetEnvironmentVariable(SplashPageConsts.EnvConnectionStringName)
                        ?? options.ConnectionString;

                    SplashPageDbContextConfigurer.Configure(options.DbContextOptions, connectionString);
                }
            });
        }
    }

    public override void Initialize()
    {
        IocManager.RegisterAssemblyByConvention(typeof(SplashPageEntityFrameworkModule).GetAssembly());
    }

    public override void PostInitialize()
    {
        if (!SkipDbSeed)
        {
            SeedHelper.SeedHostDb(IocManager);
        }
    }
}

Then REMOVE from Web.Mvc

File: src/SplashPage.Web.Mvc/Startup/Program.cs

public static void Main(string[] args)
{
    // ❌ REMOVE THESE LINES (now in EntityFramework module)
    // AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);
    // AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

    CreateHostBuilder(args).Build().Run();
}

Pros

• ✅ Centralized configuration in one place
• ✅ DRY principle - don't repeat yourself
• ✅ Automatic for all projects (Web.Host, Web.Mvc, Migrator, etc.)
• ✅ Better architecture - configuration lives with data access layer

Cons

• ⚠️ Requires testing across all projects
• ⚠️ Still uses "legacy" mode

---

Solution 3: Normalize DateTimes in Mapping Layer (CLEANEST - No Legacy Mode)

Priority: ⭐⭐⭐ (Medium) Effort: ⏱️ 30-60 minutes Risk: 🔴 Higher (requires thorough testing)

Implementation

File: src/SplashPage.Application/Extensions/SplashWifiScanningReportExtensions.cs

using Abp.Collections.Extensions;
using Abp.Extensions;
using Abp.Linq.Extensions;
using SplashPage.Splash;
using SplashPage.Splash.Dto;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Text;
using System.Threading.Tasks;

namespace SplashPage.Extensions
{
    public static class SplashWifiScanningReportExtensions
    {
        public static List<SplashWifiScanningReportDto> MapToEntityDtos(this List<SplashWifiScanningReport> entities)
        {
            return entities.Select(entity => new SplashWifiScanningReportDto
            {
                // ✅ UPDATED: Always specify UTC kind when mapping DateTimes
                DetectionDate = NormalizeToUtc(entity.DetectionDate),
                FirstDetection = NormalizeToUtc(entity.FirstDetection),
                LastDetection = NormalizeToUtc(entity.LastDetection),

                // Rest of properties
                MacAddress = entity.MacAddress,
                AccessPointModel = entity.AccessPointModel,
                AccessPointName = entity.AccessPointName,
                OrganizationName = entity.OrganizationName,
                SSID = entity.SSID,
                Manufacturer = entity.Manufacturer,
                OS = entity.OS,
                PersonType = entity.PersonType,
                PresenceCategory = entity.PresenceCategory,
                DurationInMinutes = entity.DurationInMinutes,
                DetectionsCount = entity.DetectionsCount,
                IsRegisteredUser = entity.IsRegisteredUser,
                AverageRssi = entity.AverageRssi,
                DayName = entity.DayName,
                DayNumber = entity.DayNumber,
                DayOfWeek = entity.DayOfWeek,
                MonthName = entity.MonthName,
                MonthNumber = entity.MonthNumber,
                Year = entity.Year,
                MaximumRssi = entity.MaximumRssi,
                MinimumRssi = entity.MinimumRssi,
                WeekNumber = entity.WeekNumber,
                NetworkName = entity.NetworkName,
                NetworkId = entity.NetworkId,
            }).ToList();
        }

        /// <summary>
        /// Normalizes DateTime to UTC, handling all DateTimeKind cases
        /// </summary>
        private static DateTime NormalizeToUtc(DateTime dateTime)
        {
            return dateTime.Kind switch
            {
                DateTimeKind.Utc => dateTime,
                DateTimeKind.Local => dateTime.ToUniversalTime(),
                DateTimeKind.Unspecified => DateTime.SpecifyKind(dateTime, DateTimeKind.Utc),
                _ => DateTime.SpecifyKind(dateTime, DateTimeKind.Utc)
            };
        }
    }
}

Also Update Connection Report Extensions

File: src/SplashPage.Application/Extensions/SplashWifiConnectionReportExtensions.cs

public static List<SplashWifiConnectionReportDto> MapToEntityDtos(this List<SplashWifiConnectionReportUnique> entities)
{
    return entities.Select(entity => new SplashWifiConnectionReportDto
    {
        // ✅ UPDATED: Normalize all DateTime properties
        ConnectionDateTime = NormalizeToUtc(entity.ConnectionDateTime),
        FirstSeen = NormalizeToUtc(entity.FirstSeen),
        LastSeen = NormalizeToUtc(entity.LastSeen),

        // ... rest of properties
    }).ToList();
}

private static DateTime NormalizeToUtc(DateTime dateTime)
{
    return dateTime.Kind switch
    {
        DateTimeKind.Utc => dateTime,
        DateTimeKind.Local => dateTime.ToUniversalTime(),
        DateTimeKind.Unspecified => DateTime.SpecifyKind(dateTime, DateTimeKind.Utc),
        _ => DateTime.SpecifyKind(dateTime, DateTimeKind.Utc)
    };
}

Pros

• ✅ No legacy mode - uses modern Npgsql behavior
• ✅ Explicit DateTime handling - easier to debug
• ✅ Defense in depth - normalizes at mapping layer
• ✅ Future-proof - won't break when Npgsql removes legacy mode

Cons

• ⚠️ More code to maintain
• ⚠️ Requires changes in multiple extension files
• ⚠️ Performance overhead (minimal) for DateTime normalization
• ⚠️ Must be applied consistently across all mappings

---

🏆 Final Recommendation

Hybrid Approach (Best of All Worlds)

Phase 1: Immediate Fix (Deploy Today)

1. Implement Solution 1 in Web.Host
2. Test Top5BranchesMetrics endpoint
3. Deploy to production

Phase 2: Centralization (Next Sprint)

1. Implement Solution 2 - move switches to EntityFramework module
2. Remove switches from Web.Mvc and Web.Host
3. Test all projects

Phase 3: Future-Proofing (Optional - When Npgsql Deprecates Legacy Mode)

1. Implement Solution 3 - add UTC normalization to mappings
2. Remove legacy switches
3. Thoroughly test all DateTime operations

---

📋 Testing Checklist

Before Fix

• Confirm error in Web.Host: Top5BranchesMetrics with test payload
• Confirm success in Web.Mvc: Same endpoint, same payload
• Document exact error message and stack trace

After Solution 1

• Test Top5BranchesMetrics in Web.Host - should succeed
• Test TopLoyalUsers - should still work
• Test RecoveryRate - should still work
• Test ReturnRate - should still work
• Test all dashboard widgets with date filters

After Solution 2 (if implemented)

• Test in Web.Host
• Test in Web.Mvc
• Test in Migrator (database migrations)
• Test background workers (if they use EF Core)

After Solution 3 (if implemented)

• Run full integration tests
• Test with different timezones
• Performance test (ensure normalization doesn't slow queries)

---

📚 References

Npgsql Documentation

• DateTime and Timestamps
• Legacy Timestamp Behavior

Related Files

• src/SplashPage.EntityFrameworkCore/EntityFrameworkCore/SplashPageDbContext.cs (Lines 61-70)
• src/SplashPage.Web.Mvc/Startup/Program.cs (Lines 15-16)
• src/SplashPage.Web.Host/Startup/Program.cs (Lines 10-12)
• src/SplashPage.Application/Splash/SplashMetricsQueryService.cs (Line 130, 162)

---

🔧 Troubleshooting

If error persists after Solution 1

1. Verify switches are set:

// Add debug logging in Program.Main()
var legacyBehavior = AppContext.GetData("Npgsql.EnableLegacyTimestampBehavior");
Console.WriteLine($"Legacy behavior enabled: {legacyBehavior}");

2. Check Npgsql version:

dotnet list package | grep Npgsql
# Should be 9.0.4 or compatible

3. Verify connection string:

// In appsettings.json
{
  "ConnectionStrings": {
    "Default": "Host=...;Database=...;Username=...;Password=...;Timezone=UTC"
  }
}

---

Document Version: 1.0 Last Updated: 2025-10-22 Author: Senior Backend Analysis Status: Ready for Implementation