Datetime Made Easy with This ONE C# Dapper Trick!

hi I'm Nick centino and I'm a principal software engineering manager at Microsoft in this video we're going to be looking at date time datetime offset and Dapper with a SQL database now the reason that this is important is because there are a couple of gachas if you're not paying attention to the format of the dates that you're getting back so I wanted to make this video to walk through how you can see this happening and then a solution to help work around it if that sounds interesting just a reminder to subscribe to the channel and check out that pin comment for my courses on dome train let's jump over to visual studio and check things out all right on my screen right now I have a very simple sample application and all that we're going to be doing just to keep it very simple is connecting to a local mySQL database that I'm running on my own machine what we're going to do from there is clear out the table just so that I can rerun this if I need to so that's going to be this line here on line 22 we'll clear out the table and then I'm just going to insert one record so it's into a table with one single column that's a datetime column and then we're going to add the current time into that that's all that this part here is going to do from line 21 to 25 after that all that we're going to do is read that back so again very simple we're just going to read it back and then print out all of the results that we have so if we go run this we can see how it works okay nothing too fancy with respect to the results that we're seeing here as you might expect we're printing out just this line here and then right after since we only have one record that we inserted we're just printing out that whole record and that's why you see our record so that's the two string call on the record type and it only has one property which is called date time and if we check it out this is going to be just the UTC version of the datetime so if I look at my clock right now it says 3:53 p.m. but this time that we're seeing here is at 10 5255 so that's going to be because of the time zone difference okay so this is in fact printing out the UTC version of what we're seeing and that's expected right because what I was doing was taking UTC now so this is great this is exactly what we would expect but things start to get a little bit interesting if we start working with that time that we read back so what I want to do is put in a little bit of a check here so on line 34 to 35 what I'm going to do is take the date time and I'm going to use xunit just to have this assert equal syntax here I'm going to check to make sure that the daytime we're reading in is going to be equal to the current time or the time that I took a snapshot of up here on line 21 this parameter here is just because they're not going to be exactly equal and that's simply because there isn't more resolution in the database than we have in memory working in net with dates and times we're able to get up to six decimal places of date time accuracy when working with mySQL but obviously in net we're able to get a lot more than that so what I'm doing is just saying that I'm going to have this uh this tolerance of 100 millisecond so if we go run this we should see that we don't don't have an assertion exception so let's go ahead and try that and perfect okay so good news that date that we're reading back in again it's not going to be exact and I can prove it if I take this tolerance out so let me run that again I should have showed you this part first right this throws an exception if you have a quick look you'll notice that it's saying that they're not the exact same but we can see that we have an 8 n and then an 80 here so they going to be a little different but that's why when we put this tolerance in we can accept that there's going to be a bit of a difference so so far so good before we move on this is just a quick reminder that I do have a course on C refactoring available on dome train refactoring is one of the most critical skills that you can learn as a software engineer and this helps you continue to build upon applications that already exist making sure that they can scale and have extensibility I walk you through a bunch of various techniques and give you some examples that we walk through together to see how we can apply these techniques to refactor the code check out the pin comment and the links in the description to get this course now back to the video for the applications that I've started building I end up using date time when I'm working with the records that I'm inserting into the database and reading back so if we go look at the record that we have declared down here I do have on line 46 and 47 just this hour record very simple type and it has a date time type on it what I usually do from there is that I like having things in this newer datetime off offet type that we have access to and that way I don't have to label everything as something like datetime UTC everywhere because in my systems everything's in UTC anyway so I don't want to have to name everything that way I just want to use this datetime offset type that tells me the offset within it as well so I end up using this type of pattern so what we can do is take the date time property and then we can put it directly into this date time offset but if we go do this let's go see what happens now we run into this interesting problem where the lines above or we're checking just the datetime this part works this is accurate but when we go to put it into a datetime offset all of a sudden we see that the difference is now almost 7 hours right it's like a a rounding error but that's almost 7 hours of difference and that's a huge difference right that doesn't really make sense because we saw that this is the exact same so if we do another check let's go print out what that difference is right so if I go right this out to the console we can see that when I print out the difference it saying it's only you know something that's very very very small it's way less than 1 second certainly less than 100 milliseconds in terms of that tolerance so we have something weird going on here right and it might be obvious to you already if you're kind of familiar with this type of thing but if you're not let me try to explain it a little bit more when we're working with this one on line 40 and I'm doing a date time and subtracting now What's Happening Here is that it's able to go calculate the time difference but as soon as we put it into this date time offset and we try to do a similar thing unfortunately something that's happening is that there's a loss of information or it's a different assumption about the date time that we're working with and that is that when we go to do this line on 37 it does not know what kind of date time it is and you might not have heard about this before it might not be obvious but once I show you it will be a little bit more obvious so there is literally a kind property on date times and this will tell us if it's UTC unspecified or local time so instead let me comment this part back out if we go run this and we look at the kind that's coming back on the date time that we read back in from Dapper from our database we can see that there's something interesting here it says the kind is unspecified now if we think about what's going on here early on in the program we said UTC now on line 21 then we wrote that out to the database and when we read it back in it certainly looks like it's a UTC Tim stamp right it's a little bit later than what I said earlier in the video but this time is still accurate in terms of the UTC Tim stamp now if we look at the difference between what's in memory and what we read back in from the database this also looks right but the kind of this date time is not actually marked as UTC it is marked as unspecified and that means that when we go to do this part here and we go to take that date time and put it into a date time offset it is not able to assume that it is UTC there is a little trick that we can do and I'm going to show you two different ways that we can do this the first one I find is a little bit more repetitive you have to remember to do it but I will show you a second way that allows you to do it sort of in one spot if all of your dates and times are going to be in UTC when you're working with your database so we can do something a little bit different than line 37 and what we're going to do is change the kind of the date type so we can say date time specify kind and then we can give it a datetime kind and so we'll say kind and then I'm going to say UTC right so right now it was local and let me put this onto a new line to make it a little bit more readable it was local the first way now it's going to be as UTC and that means if we go to run this now the assertion actually passes and that's because we are doing this part here and changing the kind I'm still writing out to the console the result date time kind so this unspecified is still the original one but when we go to do this datetime offset now and we go to actually do the assertion on line 41 this would have thrown an exception before now it's passing because it is the right time zone offset we do know that it's UTC but that would mean that everywhere that you're using Dapper and working with dates and times you need to remember to do this and if you're using UTC date formats everywhere it's a bit of a pain in the butt and like I said you have to remember to do it if you forget in one spot all of a sudden you can run into a lot of trouble when you're putting dates and times back into datetime offset so here's something else that you can do instead we are going to go put this back to having just the regular assert that we had and we're not going to do any time zone changing we're not specifying the date time kind at all but we are going to hook into Dapper with our own converter the code for the converter class that we have is very very simple and that's great cuz Dapper affords us a lot of flexibility in this regard so we're going to inherit from a SQL mapper type Handler and the type that we're going to be working with is date time so we just give it a name you can call it anything you'd like but this is date time Handler and then we have to go Implement these two methods so when we are setting the value this is just setting the value on the parameter here so we can just do it directly there's nothing to do when we're going to write this information out it's already working as expected so this looks pretty simple pretty straightforward but the parsing side is where the magic comes in because when we take in the object so this value here it is going to be of type date time because of this pattern up the top here so we know that we can directly cast but what we're doing is every time we are going to set it to UTC now when we go to hook this up this means that anytime Dapper is Reading in a date time it's going to force this logic right here it will always specify every date time that it reads in as UTC for the kind now if we go back up to the top of the application here what I can do right at the beginning on line n and you can put this anywhere before you're executing your SQL commands using Dapper we can say SQL mapper and we can add a type Handler and just give it a new instance of our type Handler that's all that I've changed I've just wired up this Handler which is going to affect every single date time that we have when we read it in and if I go back to here in this Loop remember I just put these assertions back this first one was always working but this second one was failing because the datetime offset was not able to know what kind we had on the date time so let's go run this again and see what the output looks like good news no crashes right when we look at the results that are printed out we still have this date time as we might expect yes it's a little bit later than before just because time is actually elapsing as I'm recording this video but when we look at the difference we have zero now and when we look at the kind we do have UTC it's no longer unspecified and that's because we are reading it in it's going through that Handler and then we're forcing it to be UTC and just to prove to you that that Magic's happening if the console output wasn't enough if I put a break point into here we can go see when the value comes in we have 11528 p.m. and then we're calling this method here to actually specify the kind so we press F5 and when we look at the output there we go it's UTC once again right so this Handler is a great way that you can add this into your application if you don't want to specify the kind everywhere and I do recommend that you are specifying kind in some way right if you want to use this Handler great I think it makes it very simple if you're a fan of explicitly setting it and you want to do that everywhere if that's what you prefer that will work as well but the implicit way when we go back to what I had up here this is not a great pattern if you are not sure coming out of the database what the kind is of your date time so you could apply this to different orms that you might be working with but I just want you to keep in mind when you do this implicit cast here you could run into some trouble in fact in the application that I'm building brand ghost I had a whole bunch of C that's been running for months and it's been working totally fine no issues but what was happening is that I had some instances where this kind of pattern was happening and when I started writing more tests I went wait a second why is everything off and then I realized that all of the date times in my application when I was reading them in all of them are supposed to be UTC and every single one of them was unspecified so for me the great solution was going and using using this Handler which is why I wanted to show it to you I hope you found that helpful and I hope you're careful with your date times and your offsets thanks and I'll see you next time