midnight in utc offset by tenant timezone #9521

BobIngham created 3 years ago

dotnetcore, angular, aspnet framework Zero 6.8.0 I am working with stored procedures and need to pass a "From" date parameter which is midnight in utc as calculated by the timezone of the tenant. In my web module I have:

Clock.Provider = ClockProviders.Utc;

I have a tenant in the UK: (currently GMT + 1) and a tenant in South Africa: (currently GMT + 2)

The relevant method in my SQLRepository class is set up as follows:

public async Task<IQueryable<GetEntitiesForKendoGrid_Result>> GetNcEntitiesForKendoGrid(int tenantId, long userId, int isDeleted)
{
    await EnsureConnectionOpen();

    var parms = new SqlParameter[4];
    parms[0] = new SqlParameter("@TenantId", tenantId);
    parms[1] = new SqlParameter("@UserId", userId);
    parms[2] = new SqlParameter("@IsDeleted", isDeleted);
    parms[3] = new SqlParameter("@From", Clock.Now.Date.ToUniversalTime());


    using (var command = CreateCommand("GetEntitiesForKendoGrid", CommandType.StoredProcedure, parms))
    .. code removed for brevity

Times in my database are all set to UTC. Regardless of changing the timezone for the client the following code line:

var timeZoneInfo = await _timeZoneService.GetDefaultTimezoneAsync(SettingScopes.Tenant, AbpSession.TenantId);

always gives me "GMT Standard Time"

The purpose of the exercise is to return midnight last night in UTC. My UK value should be "2020-08-17 23:00" My South African value should be "2020-08-17 22:00"

I have tried to inject ITimeZoneConverter to get something like midnight:

var midnight = _timeZoneConverter.Convert(Clock.Now.Date);

which gives me "2020-08-18 00:00".

What am I doing wrong to get the incorrect timezone from settings?
How do I get the value of midnight last night offset by the relevant timezone?

The nearest I have come to finding a solution is:

public async Task<IQueryable<GetEntitiesForKendoGrid_Result>> GetNcEntitiesForKendoGrid(int tenantId, long userId, int isDeleted)
{
    await EnsureConnectionOpen();

    var timeZoneInfo = await _timeZoneService.GetDefaultTimezoneAsync(SettingScopes.Tenant, AbpSession.TenantId);
    var tz = TimeZoneInfo.FindSystemTimeZoneById(timeZoneInfo);
    var midnight = new DateTime(Clock.Now.Date.Ticks, DateTimeKind.Unspecified);
    var midnightUTC = TimeZoneInfo.ConvertTimeToUtc(midnight, tz);

    var parms = new SqlParameter[4];
    parms[0] = new SqlParameter("@TenantId", tenantId);
    parms[1] = new SqlParameter("@UserId", userId);
    parms[2] = new SqlParameter("@IsDeleted", isDeleted);
    parms[3] = new SqlParameter("@From", midnightUTC);


    using (var command = CreateCommand("GetEntitiesForKendoGrid", CommandType.StoredProcedure, parms))
    .. code removed for brevity

Which seems a little cumbersome but I still can't figure out why i can'e get the relevant timezone for the tenant. Any help most appreciated.

midnight in utc offset by tenant timezone #9521

2 Answer(s)