+972 52-548-6969
ModelingDate vs. DateTime
I got a really interesting comment on a blog post talking about query optimization. The context was that working with dates is much easier for a query engine than working with date & time at the millisecond granularity. You can read the details on that in the post. Here, I want to focus on the modeling difference between the two.
In real-life scenarios, DateTime.Date is kind of useless. I don't know if any application will use such a query unless they are a UK-only application or an application that says they only deal with UTC. In practice, it is often hour or minute precision due to the time zone.. The majority of time zones are hourly based, with some operating on minute bases. e.g. 15 or 30 minutes. (Newfoundland UTC-03:30, India UTC+5:30, Eucla UTC+8:45)
This is a great example of the difference in thinking between developers and business people. Because we typically work with date & time, we tend to assume that all the associated considerations for time also apply to dates. However, that isn’t actually the case.
The date 2024-05-13 is a date. It is the same in the UK and in India. The start time for that date may be different, but the date is the same. A date doesn’t have a time zone. Mostly because that isn’t a meaningful distinction.
Let’s consider the most famous date of all, your birthday. You were born at a given point in time (date time) in a particular location (time zone). However, for the vast majority of scenarios, such details are irrelevant.
If you were born on Baker Island (UTC-12) and currently reside in Line Islands (UTC+14), you’ll celebrate your birthday based on the date, not the time. That is made obvious when you consider that a year is not an exact measurement in terms of time and the duration of time within a year varies considerably between different years.
When we talk about businesses and dates, it really gets more complex. Consider the simplest scenario, we have a business that has stores in Honolulu, Hawaii, and Dallas, Texas. On Dec 31, 2023, a purchase was made in the Honolulu store at 8PM. That is already Jan 1st, 2024 in Dallas, mind. What year would taxes be paid on that sale?
You can make all sorts of cases here, for paying that in the current year or the previous one, etc. In practice, it doesn’t matter. The date the sale was made is what determines the tax year. That means that a sale in Honolulu will be registered in 2023, while a sale that happened half an hour earlier in Dallas will be registered for 2024.
The reason for that is simply that there is no really good answer here when you start working across time zones. And trying to maintain the distinction between when the day started is not really meaningful from a business perspective.
Here is another consideration, if I signed an office lease for 6 months starting on January 1st. However, in March, daylight savings time came into effect. When is my lease going to expire?
The answer is May 31, 23:59, regardless of changes in DST. Because the duration is in months (and days) not in terms of time(and hours).
There are scenarios in business that I care deeply about the time that passed. A great example would be for payroll purposes. I did a night shift with daylight savings time in it. You bet that I want to get paid for the total number of hours that passed, not the difference between the hours on the clock. For fun, it gets really complex when you have shifts that cross payroll periods (how do you calculate overtime), but that is a topic for another time (pun intended).
The common case, however, is that you only care about the date, and the timezone is not relevant.
More posts in "Modeling" series:
- (28 Oct 2014) The grocery store’s checkout model process approach
- (21 Oct 2014) The grocery store’s checkout model
- (06 Jun 2014) Flights & Travelers
Comments
As someone who works in insurance, this is something I think about, and a message I have to reinforce often. We work with insurance policies, which are effective and expire on a particular date. We do not care about the time component. I've seen production bugs in systems because the system stored the time component and didn't correctly handle DST, so when we crossed a DST boundary, the time would shift by an hour from midnight to 1:00 AM. Fortunately, this was an internal issue since the printed documents (that define the legal contract) did not print the time, so it only affected reporting/analytics.
The companies I've worked for had specific language in the policy documents along the lines of "Coverage is effective on 12/30/2023 at midnight through 12/29/2024 at 11:59 PM". You may notice that this leaves some wiggle room for lawyers to debate over "who's time are we using" in court, but from a technical perspective, not storing a time component allows leave the problem above for the lawyers.
I wouldn't agree with your conclusion, first and for most, I am not business people and I am developer. Just to clarify. Since all your example historically around payment, invoice, order. Now I sort of get why your view seems a bit narrow.
In the product and area I am working on, time is essential, cross timezone is also norm. I'm working on ticketing system, or issue if you would like to call that. Every graph hat we are making, every information and filter we are making are all time based. Often at least minute accuracy is must due to timezone difference. Where second or millisecond difference is must for ordering.
Here is GitHub's contribution for Simon Cropp, one of the well known .net developer. I have taken screenshot of his contribution from two different timezone. USA and NZ. Every time I go to that page, it will recalculate based on my current timezone.
Take screenshot from USA timezone
Taking screenshot from NZ timezone
In ticketing system all we care about is things such as first response time, how long has this ticket been idle, who was the user last respond to the ticket and how long ago was that. Time zone is also easy in our world, where as long as you save everything as UTC, then you just query block that associated with current timezone.
As for ordering, a good example is the timeline, which include chat time line (such as article comment), communication timeline, ticket activity timeline (include change), none of them will ignore timezone. It does not matter which country and which timezone you post from, it has to be ordered correctly.
Since you have talked about birthday and saying in most of time we only care about date. Are we? Use Office's Calendar as an example. Any schedule is not date based but time based. When everyone is under same timezone sure, but the meeting on my schedule often cross timezone. My colleague could be working from Europe. We setup schedule on 1PM meeting, that's for my NZT, on his calendar it will showing in his timezone. So when query all the meeting for that day, it will have to aware of such difference. You could also argue Office saved time individually for each person. Sure, what about I'm on a flight travel to USA. So office still showing my meeting is on 1PM USA time? Of course not. It will automatically adjust.
The last example is audit log, or log in general. Such as application insight. Time query and ordering is one of essential key aspect of those system. The order provide hint to discover and resolve issue. And log are often millisecond back to back been recorded.
Different field have different perspective on date and time, that's for sure. I don't think my original post start well by assume everyone is on same view as mine, which is clearly not.
Completely forgot, as for day light saving. New Zealand originally proposed day light saving. Many countries followed. Personally I hate it. Fun fact is, most of Europe country have abandoned day light saving, since it is essentially useless and cause huge waste in business sector. (USA has 3 time zone, AUS has 2 time zone, plus the start of the day light saving, the business schedule, meeting etc all became over complicated, that's where the waste came from).
USA on the other hand proposed removal of the day light saving system a while back. Then in recent year when I check again, the bill didn't pass. So, there is always chance to correct the debt, but whether a country has the capability and willingness to correct it, only time could tell.
Jason, The example with GitHub timeline is a great one. It shows exactly what the issue is. And in particular, there is a correct frame of reference for that time, which is wherever Simon was at the time he did the work. The fact that you are looking at it from two different perspectives and getting different results is a _bug_, not a feature.
The calendar meeting and the flights are both great examples of when the time does matter. And you need to be aware of time zones very explicitly. However, those are scenarios where you care about the time component. And that is a valid use case, but tends to be pretty rare compared to just needing the date. Note that sorting audit logs by time is a bad idea. Especially if you are writing from multiple machines as in the case of application insights. Different machines have different clocks. In the cloud, you may have a 10 seconds difference between clocks of machines. That is super confusing when you look at the log and try to construct a reasonable idea about what happens when the order of events in the action and in the log is vastly different. Beyond that, time and time handling absolutely sucks. And we generally have to keep track of everything in a single calendar for businesses (the Gregorian one).Consider the fact that there are many in-use calendars. But I won't expect you to try to keep track of the 7th of Iyyar, 5784 or the difference in time between that and Dhuʻl-Qiʻdah 7, 1445 AH. Regarding your daylight savings time - consider the fact that the Islamic calendar is not in fact a unified one. And you have different "dates" (a bit more complex than that, observance times) happening on different days because of observations from different locations. In other words, the fact that your city got a cloudy day may impact what the "date" is.
Oren. You are correct on audit logs, if we only have single server that handling save the data, then sure, the time record is fine. But if we record from different device, the world is not perfect. I had server that went into future once, jump at least 8 months ahead of others.
The key that I bring those example up is that up to second ordering matters. I'm not talking about accuracy. Let's leave that aside. If you looking at outlook, you can order by date, the name says by date, it is actually sort by arrive time. In MSFT graph API, you can also filter email by received time. It is up to at least second accuracy.
As for the commit count per day grid by GitHub, I do have different opinion than you are. It is feature not bug. As a user's perspective, I want to see other's commitment by my time zone. Not by his time zone.. Especially he could be travel around as well. I want to know exactly at the time of my interest, whether he did any commit or not.
In managed service system, we do record agent's time entry. Which consist of start and end time. I did use RavenDB to summarize similar grid to give insight of how many hours been billed on any given day. We do need a static time zone for invoice, but for point of interest, especially when agent could be working from multiple time zone, it is really hard to assume which time zone we should use.. Should we use submitter's time zone? Or should we use Organization specified time zone? What if they are multi-national company? Such as Microsoft?
From my believe, GitHub intended to show commits by the view's time zone. From how they count the commit, they have state they record everything by UTC. So they are not record based on local time.
Anyway, I will leave that aside, since different people have different opinion on that. I may change my mind in the future, but as for now, for our product goes. Time does matter.
My thoughts are aligned with the post.
Using date and time over many years, as engineering manage, I've now had to dictate to my architects and developers never to use DateTime - use DateOnly, TimeOnly or DateTimeOffset. Times should always be in the timezone where the instance occurred. If someone in NZ creates a booking, it's relevant to them. If I look at the booking in AU then, if I really need to, I can convert.
And the worst of all time is converting everything to UTC - you're throwing away information.
And when printing etc, use ISO 8601 format. Other formats (looking at you here USA) are soooo confusing and have caused no end of bugs and issues.
@Simon sometimes it's the other way - i remember having a lot of trouble with a medical application that registered date and time of patient-related events. It was really important for the doctors to have an idea when some symptom occurred - in the morning, afternoon, or at night. But because of the representation of timestamp we had, and Windows APIs conspiring to handle timezones correctly, the time would jump forward or backward depending on the timezone of the doctor looking at the data. Technically it was a correct timestamp, but useless to the doctors. And lots of chaos in development because everyone was trying to use windows time apis in some crazy way to undo built-in timezone conversion.
I am using the central headquarter's time in my DWH, which is not UTC, but in Asia (+09:00).
All our worldwide subsidiaries are using these HQ's dates, when it is relevant to the HQ's reporting. For local they are using their local times of course.
After short time everyone can adjust/learn the central time and do the mental adjustments. Although it seems like an additional burden, it is actually very practical, as everyone knows what time they are referring to.
As a side-story, it seems all stations along the Trans Siberian Railway are using central Moscow time. All the clocks are showing the same time.
Comment preview