9

The pre-existing SQL Server database we work with stores a 'period' as inclusive start -> inclusive end UTC DateTime values. (Both start & end columns are datetime2(7), automatically-converted to System.DateTime instances of DateTimeKind.UTC before we start working with them).

So, if I need to store the "whole day/month/year, given the user's time-zone" I need to find "The last possible instant of a specified LocalDate, in a particular DateTimeZone".

The methods I have are as follows:

public static LocalDateTime AtEndOfDay(this LocalDate localDate)
{
    return localDate
        .PlusDays(1)
        .AtMidnight()
        .PlusTicks(-1);
}

public static ZonedDateTime AtEndOfDay(this DateTimeZone zone, LocalDate localDate)
{
    return zone
        .AtStartOfDay(localDate.PlusDays(1))
        .Plus(Duration.FromTicks(-1));
}

I think I also need to avoid (anywhere else) mapping a "end of date" LocalDateTime using .AtLeniently(..) since if 23:59:59.9999999 is "skipped" and does not exist in the target DateTimeZone, then it would be mapped to the next available instant on the 'outer' side of the gap 00:00:00.000000, which would give me an exclusive ZonedDateTime value.

Amended Methods:

public static LocalDateTime AtEndOfDay(this LocalDate localDate)
{
    // TODO: Replace with localDate.At(LocalTime.MaxValue) when NodaTime 2.0 is released.
    return localDate
        .PlusDays(1)
        .AtMidnight()
        .PlusTicks(-1);
}

public static ZonedDateTime AtEndOfDay(this DateTimeZone zone, LocalDate localDate)
{
    return zone
        .AtStartOfDay(localDate.PlusDays(1))
        .Plus(-Duration.Epsilon);
}

public static ZonedDateTime AtEndOfDayInZone(this LocalDate localDate, DateTimeZone zone)
{
    return zone.AtEndOfDay(localDate);
}
Bill Tür stands with Ukraine
  • 3,425
  • 30
  • 38
  • 48
Ben Jenkinson
  • 1,806
  • 1
  • 16
  • 31
  • 2
    I'd almost always recommend using a semi-open interval instead (inclusive start, exclusive end). The values you need tend to be far easier to compute. I.e. you don't have to compensate for the resolution/precision in which the datetime values are stored (throughout their journey through your code) to ensure that you've always got the "last possible instant" – Damien_The_Unbeliever Feb 02 '15 at 10:30
  • I am aware that there are conflicting opinions on how to store intervals, but as I mentioned in my question, I'm working with an existing system and changing everything else to suit isn't really an option. – Ben Jenkinson Feb 02 '15 at 10:35
  • Sorry, I don't mean that I don't see the benefit, or don't _want_ to change the DB structure, I just don't have that as an option right now :) – Ben Jenkinson Feb 02 '15 at 10:40
  • Ah, sorry, wasn't really clear that this was an existing system. In that case, information about the database system and data type will be necessary since computing the correct value will depend on that. – Damien_The_Unbeliever Feb 02 '15 at 10:43

1 Answers1

6

Firstly, as noted in comments, any time you can work with an exclusive upper bound, that would be a good idea :)

Your AtEndOfDay method looks reasonable to me, except that I'd use Duration.Epsilon instead of Duration.FromTicks. In particular, in Noda Time 2.0 we're going to move to a precision of nanoseconds instead of ticks; Duration.Epsilon will do what you want in both cases.

For your LocalDate solution, it seems to me that we're missing a value of LocalTime.MaxValue (or EndOfDay), the largest representable LocalTime. If that were available, you could just write:

return date.At(LocalTime.MaxValue);

which would remove the same "ticks" problem as before. I'll try to remember to add that for 2.0 - although it will be documented with a comment to the effect of "Only use this if you're forced to by legacy systems" :)

One downside of adding a day and then subtracting a tick (or a nanosecond) is that it will fail on LocalDate.MaxValue. This probably isn't a practical issue, but for code within Noda Time itself, we try to avoid things like that. I wouldn't try to avoid it for the ZonedDateTime version though, as it's a rather more complicated scenario. (There are probably ways of doing it, but it wouldn't be worth it.)

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • I tried to add a check, but there doesn't seem to be a `LocalDate.MaxValue` property, is that `internal` or a NodaTime 2.0 feature? – Ben Jenkinson Feb 02 '15 at 12:23
  • @BenJenkinson: Looks like we haven't got it in 2.0 either. As I say, it's probably not a *real* problem for you - or you could just add a check to see if the year is greater than 9000 for the moment :) – Jon Skeet Feb 02 '15 at 12:39
  • @BenJenkinson: Actually, thinking about it, it'll probably need to be `MaxIsoValue` or something like that... given that different calendar systems have different max values. There's already `CalendarSystem.MaxYear` though. – Jon Skeet Feb 02 '15 at 13:29