2

I have an Windows Console application in C# which need to compare current time with two times stored in DB. Date and Time converted to UTC before inserting into DB. Now, I need to fetch those values and compare only time part with local time. Let say my dates are as follows:

DateTime dt1 = new DateTime(2013, 08, 29, 00, 00, 00)
DateTime dt2 = new DateTime(2013, 08, 29, 23, 59, 59)

as I'm converting these two dates to UTC and storing in DB, my DB values look like '28/08/2013 18:30:00' and '29/08/2013 18:00:00'.

Let say my current local time is '14:00:00' and my condition to check is, my local time should be in between time part of my DB Datetime values. Like 

if(DateTime.Now.TimeOfDay >= DBTime1 && DateTime.Now.TimeOfDay <= DBTime2)`
{
//true condition logic
}

If I convert db date time to local and compare, it is working as expected in my local system. My doubt is what if the application is hosted in server in UK and accessing it from India, will time comparison work as expected? Do we need to consider any other things like DST (Day Saving Time) or TimeZone while converting to local time?

Jon
  • 38,814
  • 81
  • 233
  • 382
Sai
  • 159
  • 1
  • 9

2 Answers2

2

There appears to be a bug in .NET (or a terrible design choice). As you can see in the reference source starting on line 1221, the DateTime comparison code simply compares the InternalTicks property, which has the non-timezone-adjusted ticks. This means that this code:

if (ticket.Expiration > DateTime.UtcNow)

behaves differently than this code:

if (ticket.Expiration.ToUniversalTime() > DateTime.UtcNow)

even when the DateTimeKind is explicitly and correctly set.

James
  • 3,551
  • 1
  • 28
  • 38
0

DateTime.Now.TimeOfDay is of type DateTimeKind.Local

DBTime1, as stored in the DB, is a Utc time, and should be "hydrated" in C# as DateTimeKind.Utc

(DateTimeKind info)

Since the DB times are UTC, when converted into local time, as long as the local time information (including timezone, DST, etc.) are set correctly, then I can't see why converting one of these to the other's format and comparing would come out wrong. In fact, it'd be easier to go the other way and just use DateTime.UtcNow:

DateTime.UtcNow.TimeOfDay >= DbTime1 && DateTime.UtcNow.TimeOfDay <= DBTime2

Just keep in mind what this means, namely that you're checking if the current time (adjusted with time zone info) is in between the 2 times above. Are you sure that DbTime1 is always smaller than DbTime2? You may need to do some checking to make sure that's the case otherwise.

DavidN
  • 5,117
  • 2
  • 20
  • 15
  • I have used `DateTime.UtcNow.TimeOfDay >= DbTime1 && DateTime.UtcNow.TimeOfDay <= DBTime2`, here the thing is my selected date time is 30/08/2013 00:00:00 and 30/08/2013 18:00:00 which are converted to UTC before inserting into DB so my DB values are 29/08/2013 18:30:00 and 30/08/2013 12:30:00. Now my if condition should be true if local time is any where between midnight(00:00:00) till evening 6PM (18:00:00). My current time is morning 10:30:00. Now if I convert my local time to UTC and compare then my if condition looks like `if(05:00:00 >= 18:30:00 && 05:00:00 <= 18:00:00)`. – Sai Aug 30 '13 at 05:01
  • As per my selected datetime range and my local time this if condition should be true but it is false as these are in UTC. So instead of converting local to UTC I have converted my DB datetime to local time and compared. Well this is working fine for me in local machine. What if I publish to UK server, where we need to consider DST? – Sai Aug 30 '13 at 05:01