2

When it comes to Periods, - is not supposed to be the inverse of +: adding one month to March 31 produces April 30. Subtracting one month from April 30 produces March 30, not March 31.

I've got a filter on database records that involves periods:

priorDate + period < today

Here, priorDate is derived from a database column, period can be configured by the user, and I need to find all records for which the condition is true.

If the period doesn't involve months or years, I can transform this into

priorDate < today - period

which allows the comparison to be moved from the client side to the database: it allows me to avoid retrieving all records just to discard the ones that don't meet the criteria.

How do I do this if the period does involve months or years?

I can assume the Gregorian calendar, and that that period is non-negative. (Specifically: I can assume priorDate + period >= priorDate for all values of priorDate, but if possible, I'd like to not rule out periods "one month minus one day" just yet.)

If I've got a period of one month, and today is April 30, then I want to figure out that the expression should become priorDate < new LocalDate(2018, 3, 30), which is easy: that's what today - period produces.

If I've got a period of one month, and today is March 30 2018, then today - period will produce February 28, but the expression should instead become a comparison to March 1: if priorDate is exactly new LocalDate(2018, 2, 28), then priorDate + period < new LocalDate(2018, 3, 30) will be true, but priorDate < new LocalDate(2018, 2, 28) will be false.

  • not at all clear what you are asking. For a start what data types are these things. – pm100 Mar 21 '18 at 15:18
  • next - just for safety always make your precedence needs clear ie do `priorDateTime < (now - period)` – pm100 Mar 21 '18 at 15:19
  • @pm100 I had tagged this [nodatime], but now edited to included a link anyway. Parentheses are obviously not going to make any difference. –  Mar 21 '18 at 15:20
  • Do you need time (hour-minute) info or days are good enough? – Francesco B. Mar 21 '18 at 15:28
  • @FrancescoB. I *think* days will be good enough, but honestly, I'm not completely certain yet. –  Mar 21 '18 at 15:35
  • So am I right in saying that you're trying to find a `targetDate` such that `priorDateTime < targetDate` is equivalent to `priorDateTime + period < now`, computing such a target date from `now` and `period`? – Jon Skeet Mar 21 '18 at 15:37
  • Just to further clarify: can I check that you only care about the Gregorian calendar system? I'm not entirely sure, but it's possible that in the Hebrew calendar system this would be impossible. It would also be good to know whether you're only dealing with dates, or date + time values. (That may not be relevant, but I'll have to think carefully.) – Jon Skeet Mar 21 '18 at 15:38
  • Also (sorry for all of the questions): will `period` always be non-negative? – Jon Skeet Mar 21 '18 at 15:39
  • @JonSkeet Mostly yes, except I realise that that is impossible if times are included: given two `LocalDateTime` variables `a` and `b`, it's possible that `a > b`, but `a + period < b + period`. I've got time information, but as I responded to Francesco B., I'm not entirely sure yet if I can safely discard that and only look at the date components. Yes, I can assume Gregorian calendar and non-negative periods. –  Mar 21 '18 at 15:41
  • Right. It may be worth reducing this to the `LocalDate` scenario with a complete set of specifications - that at least makes it a really interesting question to me :) I suspect that *if* you can solve the problem at all, the answer to that more specific question would be useful to you. The time part may be fixable by deliberately just "overfetching" by a few days and performing a secondary filter afterwards with the precise original filter. (I suspect that it would defy user expectations in most scenarios anyway, mind you...) – Jon Skeet Mar 21 '18 at 15:44
  • @JonSkeet Good point about user expectations, will edit and get rid of the `DateTime` bits. Will also try to come up with some good examples of the corner cases. –  Mar 21 '18 at 15:48
  • That would be stellar. If this turns out to be feasible and useful, I may try to add it to Noda Time 2.3 :) – Jon Skeet Mar 21 '18 at 15:50

1 Answers1

2

Given a LocalDate value d and a Period p:

If p only includes months or years:

  • If naïve addition or subtraction of p would produce an invalid date, the result is reduced to the end of the month. The resulting date will never be "rounded up" to the next month. It will have its year/month components increased exactly by the amount specified in p. Therefore:

    • d - p will produce the lowest x such that x + p == d, if there is such an x.
      In this case, v + p < d is equivalent to v < x.
    • Otherwise, d - p will produce largest x such that x + p < d.
      In this case, v + p < d is equivalent to v <= x, or v < x + Period.FromDays(1).

    Which of these two applies can be detected by comparing d - p + p to d.

    So priorDate + period < refDate is equivalent to priorDate < F(period, refDate), where F is defined as.

    LocalDate F(Period period, LocalDate refDate)
{
  var result = refDate - period;
  if (result + period != refDate)
    result += Period.FromDays(1);
  return result;
}

If p includes both days/weeks and months/years:

  • Adding or subtracting p will add or subtract the month/year components first, and the day/week components next. Moving the period to the other side of the comparison should subtract or add the day/week components first, the month/year components last. The above F doesn't work for e.g. priorDate == new LocalDate(2000, 1, 30), period == Period.FromMonths(1) + Period.FromDays(1), refDate == new LocalDate(2000, 3, 1): here, priorDate + period == refDate (because first a month is added to produce Feb 29, then a day is added to produce Mar 1) but priorDate < F(period, refDate) (because first a month is subtracted to produce Feb 1, then a day is subtracted to produce Jan 31). For that, it's important to subtract the days component first, completely contrary to how Period arithmetic normally works.

    So priorDate + period < refDate is equivalent to priorDate < G(period, refDate), where G is defined as.

    LocalDate G(Period period, LocalDate refDate)
{
    var result =
        refDate
        - new PeriodBuilder {Weeks = period.Weeks, Days = period.Days}.Build()
        - new PeriodBuilder {Years = period.Years, Months = period.Months}.Build();
    if (result + period != refDate)
        result += Period.FromDays(1);
    return result;
}

    Note: the subtraction of new PeriodBuilder {Years = period.Years, Months = period.Months}.Build() subtracts years first, months second. This order must not get reversed, unless another fixup is added. A test case that would otherwise fail is d1 == new LocalDate(2000, 2, 29), p == Period.FromYears(1) + Period.FromMonths(1), d2 == new LocalDate(2001, 3, 31). Subtracting a month from d2 produces Feb 28 2001, then subtracting a year would produce Feb 28 2000, and adding a day produces Feb 29 2000. Subtracting a year first from d2 produces Mar 31 2000, then subtracting a month produces Feb 29 2000, and adding a day produces Mar 1 2000, which is the correct result.

It looks like the assumption in my question that period is non-negative is unnecessary.