I'm developing a .Net Core 3.1 REST Api.

I have a problem with dates and time zones falling close to midnight.
I save all data on the database in UTC and the frontend calls the API passing the time in UTC.

I am currently in the GTM 2 time zone.

I have to split 'actions' performed by the user into different days.

Example of output:

    {
        days: [
            {
                day: '2021-10-28',
                actions: [
                    {
                        id: 123456,
                        actionType: 1,
                        startDate: '2021-10-28T12:04:12Z',
                        endDate: '2021-10-28T13:04:39Z'
                    },
                    {
                        ...
                    }
                ],
                actualHours: 1,
                expectedHours: 4
            },
            {
                day: '2021-10-29',
                actions: [
                    {
                        id: 123467,
                        actionType: 1,
                        startDate: '2021-10-29T12:00:45Z',
                        endDate: '2021-10-29T14:00:40Z'
                    },
                    {
                        ...
                    }
                ],
                actualHours: 2,
                expectedHours: 4
            }
        ]
    }

The problem appears when we have times close to midnight because on the database the day will be saved as the day before the one entered by the user.

Example of user that adds an action for the whole day.

Frontend side:

• startDate: 2021-10-28T00:00:00.000
• endDate: 2021-10-28T23:59:59.999

Json request:

    {
        actionType: 1,
        startDate: '2021-10-27T22:00:00.000Z',
        endDate: '2021-10-28T21:59:59.999Z'
    }

In the database the dates will be saved like:

• startDate: 2021-10-27T22:00:00.000Z
• endDate: 2021-10-28T21:59:59.999Z

To take the actions and separate them by day I wrote this code snippet..

    public async Task<IList<DailyActionItem>> GetAsync(string userId, DateTime startDate, DateTime? endDate)
    {
        // Other code here..
        
        // Get actions by dates
        var tActions = await _actionsQuery.GetPeriodAsync(userId, startDate, endDate).ConfigureAwait(false);

        // Create a list of days
        var days = new List<DailyActionItem>();

        // Cycling the range of dates and also adding the missing days
        var ed = endDate.HasValue ? endDate.Value.Date : DateTime.UtcNow.Date;
        for (var sd = startDate.Date; sd <= ed; sd = sd.AddDays(1))
        {
            var daily = new DailyActionItem
            {
                Day = sd.Date,
                Actions = new List<ActionItem>()
            };

            // Other code here..

            var actions = tActions
                    .Where(t => (t.StartDate <= sd.AddDays(1).AddTicks(-1) && t.StartDate >= sd) ||
                                (t.StartDate <= sd.AddDays(1).AddTicks(-1) && t.EndDate.HasValue && t.EndDate >= sd))
                    .OrderBy(c => c.StartDate);

            // Add actions nella risposta e calcolo i valori attuali
            if (actions?.Any() == true)
            {
                foreach (var action in actions)
                {
                    daily.Actions.Add(new ActionItem
                    {
                        Id = action.Id,
                        ActionType = action.ActionType,
                        StartDate = action.StartDate,
                        EndDate = action.EndDate,
                        Comment = action.Comment
                    });
                }

                // Other code here..
            }
            
            // Important: calculations made on the day
            daily.ActualHours = tActions.Sum(...);
            daily.ExpectedHours = contract.ExpectedHours;

            // Other code here..

            days.Add(daily);

        }

        return days;
    }

Debugging example (single day - 2021-10-28 from 00:00 local to 23:59 local):

• startDate: 2021-10-27T22:00:00.000Z
• endDate: 2021-10-28T21:59:59.999Z

In foreach:
first round -> sd = 2021-10-27T00:00:00.000Z last round -> sd = 2021-10-28T00:00:00.000Z

Output:

    {
        days: [
            {
                day: '2021-10-27',
                actions: [
                    {
                        id: 123987,
                        actionType: 1,
                        startDate: '2021-10-27T22:00:00.000Z',
                        endDate: '2021-10-28T21:59:59.999Z'
                    }
                ],
                actualHours: 24, // full day, wrong place!
                expectedHours: 4
            },
            {
                day: '2021-10-28',
                actions: [
                    {
                        ...
                    },
                    {
                        ...
                    }
                ]
            }
        ]
    }

Since the APIs work in UTC, this code split dates based on the value in UTC.
For example the last day entered '2021-10-28T00:00:00.000 LOCAL' will be placed under the day '2021-10-27 UTC'.

How can I solve this problem? What am I missing?
What is the best approach to manage these cases?

Theoretically the result is correct. But on the frontend the reading of the data is not correct.

CodePudding user response：

Looks like a strange bug. I'd suggest replacing DateTime with DateTimeOffset everywhere, make a DB migration and update database.

Tell me if above approach didn't work.