C# .Net Linq and converting datetime to formatted sting

Question

I have the query

var result =  (from myView in db.loginSessions
    where myView.machine.ToUpper().Equals(machine.ToUpper())
          && myView.start >= myStart
          && myView.end <= myEnd
    orderby myView.start
    select new loginSessionList {
        Id = myView.id,
        Machine = myView.machine.ToUpper(),
        Start = myView.start.ToString("u"),
        End = myView.end.ToString("u"),
        User = myView.username
    }).ToList();

I get ArgumentOutOfRange exceptions on the datetime conversions. I have tried different ToString conversion strings. I have tried the other To... date->string conversions offered by Intellisence. I have tried Convert.string(myView.start). Nothing has worked. I have googled and have found advice using all the things I have tried. Do I have to post-process the generated list?

What have I missed?

Whether or not you have to, it's almost always better to use strong types (`DateTime`) until you're ready to display it to the user (screen/extract/etc). So if possible, those dates should be `DateTime` in the `loginSessionList` class, and you can deal with formatting them later. — Joe Enos, Mar 12 '18 at 14:55
Thanks. This led me to the answer. I **had** been using `DateTime` in `loginSessionList` but started having issues. I changed those entries to strings and ended up asking the question above. My problem was that the field names in `loginSessionList` were once `lowercase`. Some code reformatting appears to have unhelpfully CamelCased them and that messed up what came out of de-serialization. — 7 Reeds, Mar 12 '18 at 15:29

score 0 · Answer 1 · answered Mar 12 '18 at 14:57

0

I have 3 rules for dealing with DateTimes that served me well:

Always store, retreive and transmit the UTC value. Translating into the proper local Timezone is the job of ToString(), wich asks Windows for the users timezone. You do not want to add timezones to your troubles.
Avoid store, retreive or transmission of DateTimes as strings. Keep it in proper types whenever possible
If you can not follow rule 2 (like when you deal with Serialsiation), at least pick a fixed Culture Format and String encoding on all endpoints. You do not want to get different Cultures or faulty implied Encodings to your existing troubles

answered Mar 12 '18 at 14:57

Christopher

9,634
2
17
31

That first rule isn't always a good idea. If I've scheduled a calendar event for "March 12th 2022, 3pm in London" for example, I'd want that information stored - so that if between now and then the time zone rules are changed, the event *still* represents the information that the user provided. Likewise an event with a first occurrence of "March 12th 2018, 3pm in London, recurring once every week" really needs to know the time zone in order to handle DST changes. Recording a past instant in time (e.g. a timestamp) via UTC is fine, but I wouldn't try to take it too far. – Jon Skeet Mar 12 '18 at 15:01
@JonSkeet: You want it stored that the date is in London and thus should use London Timezones during display. You do **not** want to store anything but UTC in the Backend Database. Dealing with details like changing Timezones/DST? that is something the Culture Formats and ToString() hopefully do already. Otherwise it is too much trouble to bother. – Christopher Mar 12 '18 at 15:04
Again, I strongly disagree. If you've got everything stored in UTC, are you going to run a backend job changing everything if the time zone rules ever change? (And time zone rules *do* change, very frequently.) The mantra of "only ever store UTC" is often quoted and works well for simple situations, but it's not sufficient for all applications, and shouldn't be stated in an unqualified way like this, IMO. – Jon Skeet Mar 12 '18 at 15:06
And no, a time zone is entirely separate from a culture/format. If time zone rules change, that changes **when an event occurs**. That's not the same thing as changing just a text representation. – Jon Skeet Mar 12 '18 at 15:07
@JonSkeet Translating "UTC" to "Current user selected timezone" is soemthing taht ToString() and Windows do a thousand times better then I ever could. Why would I try to re-invent the wheel, when my working car is standing right there and I have the keys? – Christopher Mar 12 '18 at 15:16
I never suggested you should perform the time zone conversion by writing that code yourself. You should use a good library to do so (I'd recommend my Noda Time library, of course). But the point is what you store. If you ask Windows to convert "March 12th 2022, 3pm in London" into UTC and then store that UTC value, you've lost information. If the time zone rules change next year so that (say) UK is permanently on UTC+1, the information in your database no longer matches what the user asked you to store. They asked you to store "March 12th 2022, 3pm in London". – Jon Skeet Mar 12 '18 at 15:18
For more information, I'd recommend reading Matt Johnson's excellent answer here: https://stackoverflow.com/questions/19626177/how-to-store-repeating-dates-keeping-in-mind-daylight-savings-time/19627330#19627330 – Jon Skeet Mar 12 '18 at 15:22
"If the time zone rules change next year so that (say) UK is permanently on UTC+1" then it really depends on how hte other side handles it. Did the Meeting stay at the same UTC time or did it adapted to local time? In either case you are wrong half the time and have to ask the other side. – Christopher Mar 12 '18 at 15:22
That's assuming there's an "other side". There may well not be - it may not even be a meeting at all; there are plenty of other kinds of events. But fundamentally, I'm giving an example of where the user has entrusted you the information that something will happen on "March 12th 2022, 3pm in London". Converting that to UTC loses information, so I'd suggest avoiding doing that (or at least, not *only* storing the UTC, and not *only* regarding time zone information as something connected with a string representation). – Jon Skeet Mar 12 '18 at 15:25
@JonSkeet: If the date is when a stock market outside of the UK closes and you have only 1 hour to react on the data, your Metting must stay on the same UTC or it is pointless (no data yet or window of opportuntiy passed). The Airline/Airport might have to re-shuffle flights because someone just changed their timezones, causing the most important person to come earlier/later. Another meeting might have had to move. Once timezones change, someone has to make a verficiation pass. Even in your case, someone would still have to go and change wich "Target Timezone" was meant in the DB. – Christopher Mar 12 '18 at 15:50
If the user wants to schedule their event in UTC, that's fine. But you seem to be assuming that you know better than the user, and that the user saying "March 12th 2022, 3pm in London" really doesn't know what they mean. I prefer to store the data that the user has expressed, as an ultimate source of truth. It may well be worth storing a UTC value *as well* and recomputing that when you know you're changing time zone data, but what you're advocating is *losing information* and I can't see how that's a good idea. – Jon Skeet Mar 12 '18 at 16:00

score 0 · Answer 2 · answered Mar 12 '18 at 15:38

So, the answer to my issue had nothing to do with Linq or .Net. @JoeEnos put me on the right track as mentioned in my comment to his comment. I had created a class to receive each row of query result. The date fields were initially DateTime types but I started having issues. I changed those fields to string and then ended up asking my question above.

Back when the receiving class still had the DateTime fields, all of the fields had lowercase names. I must have applied some sort of code formatting that CamelCased the field names. This meant that after serializing the results the CamelCased names could not be found and the javascript didn't care.

I fixed the field names in my js code, changed the field data types back to DateTime and all is good.

Thanks

C# .Net Linq and converting datetime to formatted sting

2 Answers2