Given 2 LocalDateTime generate filters based on days or days/hours

Question

I would like to have a method that given a time interval granularity (e.g. DAY, HOUR, etc.) and 2 LocalDateTime a and b generates a string of the following type:

let's assume a = '2018-01-01 12:23:23' and b = '2018-01-10 15:18:13' if the time interval given is

DAY: a and b are rounded to days and the output will be a string "d >= '2018-01-01' and d <= '2018-01-10"
HOUR => the granularity here is hour, so a and b are rounded to hours and the string will be "(d == '2018-01-01' and h >= 12) or (d >= '2018-01-02' and d <= '2018-01-09') or (d == '2018-01-10 and h <= 15)"

Right now, I need only these two cases, but I would like to be flexible (let's say I would like to do the same things with minutes in the future)

I couldn't find a smart way to do so, for example using Duration class in Java, but I was just able to hard code the result for the two granularities above

I wish you luck with all of that. Did you happen to have a question for us, by chance? — Joe C, Oct 31 '18 at 22:44
`d >= '2018-01-01' and d <= '2018-01-10` is the string you want to return? — shmosel, Oct 31 '18 at 22:45

score 1 · Answer 1 · answered Oct 31 '18 at 23:25

Why do you believe that using Duration for this would be a "smart way"? It's not. Your two LocalDateTime objects have all the values to need to build the text result.

Example:

public enum Granularity { DAY, HOUR }
public static String toSqlCondition(LocalDateTime min, LocalDateTime max, Granularity granularity) {
    switch (granularity) {
        case DAY:
            return "d >= '" + min.toLocalDate() + "' and d <= '" + max.toLocalDate() + "'";
        case HOUR:
            return "(d == '" + min.toLocalDate() + "' and h >= " + min.getHour() + ") or " +
                   "(d >= '" + min.toLocalDate().plusDays(1) + "' and d <= '" + max.toLocalDate().minusDays(1) + "') or " +
                   "(d == '" + max.toLocalDate() + "' and h <= " + max.getHour() + ")";
        default:
            throw new UnsupportedOperationException("Cannot build SQL condition with granularity " + granularity);
    }
}

Test

LocalDateTime a = LocalDateTime.parse("2018-01-01T12:23:23");
LocalDateTime b = LocalDateTime.parse("2018-01-10T15:18:13");
System.out.println(toSqlCondition(a, b, Granularity.DAY));
System.out.println(toSqlCondition(a, b, Granularity.HOUR));

Output

d >= '2018-01-01' and d <= '2018-01-10'
(d == '2018-01-01' and h >= 12) or (d >= '2018-01-02' and d <= '2018-01-09') or (d == '2018-01-10' and h <= 15)

right! And what if we have same day or same day and same hour? We will have redundant filters. Also, if I want to plug in minutes, I have to hardcode that again, right? — alexlipa, Oct 31 '18 at 23:31
@alexlipa If you want to optimize the result if inputs have same day, you'd have to code for that anyway, since the result will be different. And if you want a granularity of `MINUTE`, the resulting conditions will be different, so also have to code all that. You won't get any of it for free. — Andreas, Oct 31 '18 at 23:43

Given 2 LocalDateTime generate filters based on days or days/hours

1 Answers1