Warm tip: This article is reproduced from stackoverflow.com, please click
c# entity-framework-core asp.net-core-mvc database-schema

C#/EF Core: Working with (historical) dates/periods

发布于 2020-03-27 10:23:46

Given a C# website using ASP.NET Core Mvc using SQL Server and EF Core.

For a hobby project I'm looking in old newspapers, documents and searching for specific buildings. Each time I find information of such a building I try to write down date information. So that I can keep track in which period this building was there.

So let's say I come across Build X and see date 1880. I write down the date 1880. A few weeks later I come across Building X again and see date: 16/03/1877. I know now that the building was there for sure from 16/03/1877 till 1880.

Period: 16/03/1877 - 1880

  • Now some buildings don't have any date info.
  • Some buildings have only a single date (maybe with extra research another one could be found)
  • Some buildings might have information that it existed in the 17th century (without a specific year or date).
  • dates can contains year, year+month or year+month+day

Now what would be the best to store this in the database and represent this in a model.

Would 2 properties on a model be sufficient?

public class Building {
[DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
  public DateTime? Date1 { get; set; }
[DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime? Date2 { get; set; }
}

Would it be suitable for search filters on the website. E.g. searching everything between 1830 - 1930 or everything from 17th century.

With the last question, I mean, how would you write a linq query if there is only one date given for a building (e.g. 1835). Would each query contain an extra check for the nullable date in case there is only a single date given? or are there better scenarios?

.Where(x => x.Date1 != null && x.Date1 >= 1830 && (x.Date2 == null || x.Date2 <= 1930))

Or is saving a DateTime? object not a good idea and better to store a short or integer and only keep track of the year component (if any)?

Questioner
juFo
Viewed
64
Chris Pratt 2019-07-03 23:22

Really, your problem boils down to showing exact vs "fuzzy" dates. There's no one single strategy, but I've had a somewhat similar requirement before and handled it by having a start date, a fuzzy start, an end date, and a fuzzy end, all nullable.

If you know any exact portion of the date at all, you use the actual datetime field. For example, if all you know is 1880, you store it as 1880-01-01. If you know the year and month, you just make the day 01, and of course, if you know the full date, then you store that. Now, granted, this can potentially be problematic. How do you know it wasn't literally built January 1st, 1880, versus just generically 1880? Well, that's up to you to decide how to handle those situations. You can just assume that any 01 is the same as a null value for that date portion. For whatever reason, buildings don't tend to open on January 1st, or really the 1st of any month, but there could always be an exception out there.

If you do need greater precision, you might need to break the date apart and store it literally as year, month, and day - all nullable int columns. Then, you can simply piece it together however you like when it comes time to display it.

The "fuzzy" date columns would be strings and are where you'd store more nebulus "dates" like "Victorian Era", i.e. you may not have a true date or even year, but you know it was during some period of time. You could do the same with things like "19th century", but I'd personally prefer to store that as 1800-01-01 and then intuit that it should be displayed as "19th century" when there's a date of that form.

Another option is to use both the date and the fuzzy text. For example, you could store it as 1800-01-01 and "19th century", and then you can make the determination that it's not literally January 1st, 1800, based on the presence of the fuzzy text not being null. That can help solve the ambiguity problem described previously, as you could do this for all such cases. For example, if all you know is "July 1880", then you can store that in the fuzzy text, and set the date as 1880-07-01. Then, based on the presence of the fuzzy text, you can choose to decipher the 01 portion as essentially null, versus literally the first of the month. If there's no fuzzy text set, then you assume it's an exact date.

As far as representing it on your model, I wouldn't use actual DateTime properties or DisplayFormat. You'd be better served by having utility methods that can work over the logic and return a pre-formatted "date". Then you'd simply do something like @building.GetBuiltDisplay(), and return all the information you know (including start and end if you have it) already formatted.