3

I am working on an application with train schedules, where the first train leaves at 0400 while the last train leaves at 0200. The users of this application therefore deal with days starting at 0300 and ending at 0300. In other words, when they say "The Wednesday-train at 0200" they really mean "The train that leaves on Thursday at 0200".

Our application needs to store all of the trains leaving on (for example) Wednesday, which means it should not include the trains that leave before 0300 but it should include the trains that leave the next day until 0300.

How would I represent this in application without going mad? And how should this be stored in a database for easy querying?

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
Marius
  • 57,995
  • 32
  • 132
  • 151
  • A workaround would be to hold a time offset in the database which you could apply to all times to effectively "normalise" them to a 00:00 to 23:59 "day". – ChrisF Nov 18 '13 at 16:53
  • You should store it as UTC (hour change will cause a problem). By substracting 3 to the hours, you could treat it as if it was a normal day. – the_lotus Nov 18 '13 at 16:57

1 Answers1

2

I would store the actual date/time value. Then for querying, to search for "anything on Wednesday" you'd go from Wednesday 0400 (inclusive) to Thursday 0400 (exclusive).

In terms of display, you'd probably be best taking the date, and subtracting a day if the time is earlier than some cutoff:

private static readonly LocalTime CutOff = new LocalTime(4, 0, 0);

...
LocalDate date = dateTime.Date;
if (dateTime.TimeOfDay < CutOff)
{
    date = date.PlusDays(-1);
}
var dayOfWeek = date.IsoDayOfWeek;

I would try to avoid using the date on its own as far as possible, to avoid going mad. Any date/time will be unambiguous.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • "I would try to avoid using the date on its own as far as possible" so when storing the day in the database, you would store it as 2013-11-20T04:00:00Z instead of 2013-11-20T00:00:00? – Marius Nov 18 '13 at 16:57
  • @Marius: Wouldn't you store the date *and time*? When would you just need to store the date? If you were to store just the date though, you should really store *just the date* rather than midnight at that date. – Jon Skeet Nov 18 '13 at 17:04