CODEWITHSUNDEEP
c#linqentity-framework-coreutc
Ben5
Ben5

Reputation: 891

How can I query UTC-dates for the client's current day?

My goal is to query the database for entries of the current day. Let's say the user saves a row with a time of '2021-01-27 01:00' and one with '2021-01-27 00:59'. The time zone is GMT+1 so it will be stored in the database in UTC like this:

row1: '2021-01-27T 00:00Z'

row2: '2021-01-26T 23:59Z'

How can I query for rows of the 27th of January 2021 (selectedUtcDate), from the view of the client / client?

The following will only return one entry:

.Where(row => row.Date == selectedUtcDate)

Do I have to use a range to get all entries of the same date?

.Where(row => row.Date >= selectedUtcDate && row.Date < selectedUtcDate.AddDays(1))

Or is this even automatically resolved by entity framework?

Upvotes: 0

Views: 793

Answers (2)

Ben5
Ben5

Reputation: 891

So to answer the original question with help of the comments:

  • Yes you have to use a range to query dates like this.
  • And of course you need to use the client's local date converted to utc (or the offset) as Harald Coppoolse mentioned.

Upvotes: 0

Harald Coppoolse
Harald Coppoolse

Reputation: 30512

This has nothing to do with a database. The problem is, that the 24 hours or January 27th in Beijing are not the same 24 hours in Greenwich.

Internally, you use UTC. If somewhere on a computer in Beijing a date in local time is known, you know the start DateTime of that day and the start DateTime of the next day. After that you can convert these DateTime value to UTC:

DateTime localDay = FetchDateInLocalTime();  // The day in Beijing
DateTime utcDayStartTime = localDay.Date.ToUniversalTime;
DateTime utcNextDayStartTime = utcDayStartTime.AddDays(+1);

Now comes the database part:

var result = dbContext.Orders.Where(order => utcDayStartTime <= order.Date
                                          && order.Date < utNextDayStartTime);

Simple comme bonjour!

Upvotes: 1

Related Questions