How to pass UTC date from client to mongo db c# driver and filter?

Question

Is there anyway to prevent the mongodb c# driver from converting any date passed in a filter query to UTC but instead accept the date as UTC?

I am using .net core 2.1 with some telerik controls to display a grid. Inside of the header of the grid I have a filter control to filter a date range.

Inside the client event before filtering I am capturing the date and converting it to utc:

function onGridFilter(e) {
        // check if it is a date field
        if (e.filter && e.field === "created"){
            convertDateToUTC(e.filter);
        }
    }

    function convertDateToUTC(filter) {
        var filters = filter.filters;
        for (var i = 0; i < filters.length; i++) {
            if (filters[i].field === "created") {
                var date = filters[i].value;
                var isoDate = new Date(date.getUTCFullYear(), date.getUTCMonth(), date.getUTCDate(), date.getUTCHours(), date.getUTCMinutes(), date.getUTCSeconds());                    
                filter.filters[i].value = isoDate;
            }
        }
    }

In the API call I am converting the UTC date to the local time of the server like so (this API is a kubernetes microservice and can have a different time zone depending on where it is deployed):

// Update the dates to server times for filtering, mongo will accept dates and convert to UTC based on the server location
        foreach (var f in this.Filters)
        {
            if (f.ConvertedValue.GetType() == typeof(DateTime))
            {
                DateTime dt = (DateTime)f.Value;
                f.Value = dt.ToLocalTime();
            }
        }

Using the telerik filter (DataSourceRequest object) and the mongodb c# driver (linq to mongo) I am creating a mongo query to filter the records in the mongo database.

 public DataSourceResult GetCollectionQuery(string organizationId, DataSourceRequest request)
    {
        IMongoCollection<Case> casesCollection = _db.GetCollection<Case>(_collection);
        IMongoCollection<Person> personCollection = _db.GetCollection<Person>(_personCollection);
        IQueryable<CaseListViewModel> query;

        // Setup full results query
        query = (from c in casesCollection.AsQueryable()
                 where c.OrganizationId == organizationId
                 join p in personCollection.AsQueryable() on c.ClientId equals p.Id into p
                 from person in p.DefaultIfEmpty()
                 select new CaseListViewModel()
                 {
                     Id = c.Id,
                     DisplayName = person != null ? person.LastName + ", " + person.FirstName : string.Empty,
                     OrganizationCaseId = c.OrganizationCaseId,
                     ServiceName = c.ServiceName,
                     ClientType = c.ClientType,
                     Addresses = c.ClientTypeValue == ClientTypeValue.Person ? person.Addresses != null ?
                                    person.Addresses.Where(o => !o.End.HasValue).Select(o => o.AddressLine1) : null : null,
                     Worker = string.Empty, //c.Assignments,
                     Created = c.Created,
                     Status = c.Status,
                     OrganizationGeography = person != null ? person.OrganizationGeography != null ? person.OrganizationGeography.Name : string.Empty : string.Empty
                 });

        // Filter/Sort/Page results
        return query.ToDataSourceResult(request);
    }

The reason I am converting the client to UTC, UTC to server then passing that date to the mongo query is because the client and server can be in different time zones.

This seems like it is a lot of unnecessary work to filter the date on the grid. Currently this solution does work, however I am looking for an alternative on the mongodb c# driver side. I want the mongodb query to read all dates AS UTC instead of converting the dates retrieved to UTC.

I know there is a way to tell a property that it is being saved as utc with the BsonDateTimeOptions DateTimeKind:

[BsonElement(elementName: "created")]
[BsonDateTimeOptions(Kind = DateTimeKind.Utc)]
public DateTime Created { get; set; }

Is there something similar for queries?

Update: The solution was to specify what the DateTimeKind was for the date being filtered.

foreach (var f in this.Filters)
        {
            if (f.ConvertedValue.GetType() == typeof(DateTime))
            {
                DateTime dt = (DateTime)f.Value;
                dt = DateTime.SpecifyKind(dt, DateTimeKind.Utc);
                f.Value = dt;
            }
        }

Answer 1

You might try to use DateTime.SpecifyKind for your date like this:

query = (from c in casesCollection.AsQueryable()
...
select new CaseListViewModel()
{
    ...
    Created = DateTime.SpecifyKind(c.Created, DateTimeKind.Utc)
}

In this case mongodb driver should interpret the date as already in utc, so it won't perform conversion.

Answer 2

I'm not entirely sure what you're asking, but there are a few things I see in your code:

• In your client-side code, you have:

  var isoDate = new Date(date.getUTCFullYear(), date.getUTCMonth(), date.getUTCDate(), date.getUTCHours(), date.getUTCMinutes(), date.getUTCSeconds());
  

  This is a common anti-pattern, and you should not ever do that. The Date object's parameters expect values in local time, and you are passing values that are in UTC. Doing this is basically the same as adding the amount of the local time zone's UTC offset. In other words, it does not convert the object to a different time zone, it simply picks a different moment in time.

  What you probably want to do instead is date.toISOString(). This will emit a UTC-based string in ISO 8601 format that is appropriate to send to the server.

• In your server-side code, you have:

  f.Value = dt.ToLocalTime();
  

  This will convert to the server's local time zone. In the vast majority of cases, you should avoid writing code that depends on the server's time zone setting. Instead, keep the input time in UTC as it was given to you. Store using UTC, query using UTC, and return UTC-based responses. In your client-side code that receives the response, convert back to local time using the Date object, or a library.

Also, in your GetCollectionQuery, I don't see anything related to date or time at all, so I'm not sure how that relates to your question.