Method splittinga time_range in separate

[peter petros]

Hi all,
I have three materialized views in my project
MV_MSG_EXC_REC_HISTORY : Summarizes message data for the past two years up to the beginning of the current year.
MV_MSG_EXC_REC_RECENT : Summarizes message data from the beginning of the current year up to the current day.
MV_MSG_EXC_REC_CURRENT_DAY : Summarizes messages for the current day, grouping by minute-level timestamps and updating based on the nearest half-hour.

From the ui of the application I have two criteria, let it be "From" and "To"
I want when user adds these criteria to translate it to Java in a Map<String, Pair<LocalDateTime, LocalDateTime>> which contains the name of the MV, the start_date and end_date in order to construct queries like below :
eg.
select SUM(CNT)  from MV_MSG_EXC_REC_HISTORY where min_date >= start_date   and min_date <= end_date;

I want to have 4 cases :
1. Both dates are filled then -> where min_date >= start_date and min_date <= end_date
2. Left date is null then  -> where min_date <= end_date
3. Right date is null then -> where min_date >= start_date
4. Both dates null then -> no where clause

Examples :
Supposing I have the below ranges for all the cases
MV_MSG_EXC_REC_HISTORY: 2022-01-01T00:00 - 2023-12-31T23:59:59.999
MV_MSG_EXC_REC_RECENT: 2024-01-01T00:00 - 2024-11-04T23:59:59.999
MV_MSG_EXC_REC_CURRENT_DAY: 2024-11-05T00:00 - 2024-11-05T07:30:00
====================
Case 1
And user selects the below from UI
From: 2023-01-01T00:00:03
To: 2024-10-10T03:56:04.235

I want to make a Map<String, Pair<LocalDateTime, LocalDateTime>> like

Map<String, Pair<LocalDateTime, LocalDateTime>> dateRanges = new HashMap<>();
dateRanges.put(MV_MSG_EXC_REC_HISTORY, Pair.of(LocalDateTime.of(2023, 1, 1, 0, 0), null));
dateRanges.put(MV_MSG_EXC_REC_RECENT, Pair.of(null, LocalDateTime.of(2024, 10, 10, 3, 57)));

(the left date should be floored in minute level and the right date to be ceiled to next minute)

Case 2
And user selects the below from UI
From: 2023-01-01T00:00:03
To: 2024-12-31T03:56:04.235 (larger than current_date)
====================

Map<String, Pair<LocalDateTime, LocalDateTime>> dateRanges = new HashMap<>();
dateRanges.put(MV_MSG_EXC_REC_HISTORY, Pair.of(LocalDateTime.of(2023, 1, 1, 0, 0), null));
dateRanges.put(MV_MSG_EXC_REC_RECENT, Pair.of(null, null));
dateRanges.put(MV_MSG_EXC_REC_CURRENT_DAY, Pair.of(null, null));

Case 3
I have the below ranges
MV_MSG_EXC_REC_HISTORY: 2022-01-01T00:00 - 2023-12-31T23:59:59.999
MV_MSG_EXC_REC_RECENT: 2024-01-01T00:00 - 2024-11-04T23:59:59.999
MV_MSG_EXC_REC_CURRENT_DAY: 2024-11-05T00:00 - 2024-11-05T07:30:00
====================
And user selects the below from UI
From: 2023-01-01T00:00:03
To: 2024-11-03T03:56:04.235 (smaller than current_date)
====================

Map<String, Pair<LocalDateTime, LocalDateTime>> dateRanges = new HashMap<>();
dateRanges.put(MV_MSG_EXC_REC_HISTORY, Pair.of(LocalDateTime.of(2023, 1, 1, 0, 0), null));
dateRanges.put(MV_MSG_EXC_REC_RECENT, Pair.of(null, null));
dateRanges.put(MV_MSG_EXC_REC_CURRENT_DAY, Pair.of(null, LocalDateTime.of(2024, 11, 3, 3, 57)));

My code is below :

import java.io.Serializable;

public class Pair<L, R> implements Serializable {

    private static final long serialVersionUID = 1L;
    private L left;
    private R right;

    public Pair() {}

    public Pair(L left, R right) {
        this.left = left;
        this.right = right;
    }

    public static <L, R> Pair<L, R> of(L left, R right) {
        return new Pair<>(left, right);
    }

    public L left() {
        return left;
    }

    public R right() {
        return right;
    }

    public L getLeft() {
        return left;
    }

    public R getRight() {
        return right;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((left == null) ? 0 : left.hashCode());
        result = prime * result + ((right == null) ? 0 : right.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) return true;
        if (obj == null || getClass() != obj.getClass()) return false;
        
        Pair<?, ?> other = (Pair<?, ?>) obj;
        if (left == null) {
            if (other.left != null) return false;
        } else if (!left.equals(other.left)) return false;

        if (right == null) {
            if (other.right != null) return false;
        } else if (!right.equals(other.right)) return false;

        return true;
    }
}

import java.time.LocalDateTime;
import java.util.AbstractMap;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

class Main {
    private static final String MV_MSG_EXC_REC_HISTORY = "MV_MSG_EXC_REC_HISTORY";
    private static final String MV_MSG_EXC_REC_RECENT = "MV_MSG_EXC_REC_RECENT";
    private static final String MV_MSG_EXC_REC_CURRENT_DAY = "MV_MSG_EXC_REC_CURRENT_DAY";

    private List<Pair<String, Object>> params;

    public static void main(String[] args) {
        // Example usage of getDbMvRanges
        Map<String, Pair<LocalDateTime, LocalDateTime>> ranges = getDbMvRanges();
        ranges.forEach((key, value) -> {
            System.out.println(key + ": " + value.getLeft() + " - " + value.getRight());
        });

        System.out.println("====================");
        
        // Example usage of getUiDates
        Pair<LocalDateTime, LocalDateTime> uiDates = getUiDates();
        System.out.println("\nFrom: " + uiDates.getLeft());
        System.out.println("To: " + uiDates.getRight());
        
        System.out.println("====================");
        
        // Example usage of getMvNames
         LocalDateTime currentTime = LocalDateTime.now();
        Map<String, Pair<LocalDateTime, LocalDateTime>> mvNames = getMvNames(currentTime);
        mvNames.forEach((key, value) -> {
            System.out.println(key + ": " + value.getLeft() + " - " + value.getRight());
        });
    }

    private static Pair<LocalDateTime, LocalDateTime> getUiDates() {
        LocalDateTime fromDate = LocalDateTime.of(2024, 1, 1, 0, 0, 0);
        LocalDateTime toDate = LocalDateTime.of(2024, 10, 10, 3, 56, 4, 235_000_000);
        return Pair.of(fromDate, toDate);
    }
    
    private static Map<String, Pair<LocalDateTime, LocalDateTime>> getDbMvRanges() {
        Map<String, Pair<LocalDateTime, LocalDateTime>> ranges = new LinkedHashMap<>();
        
        // Define the date ranges based on your requirements
        ranges.put(MV_MSG_EXC_REC_HISTORY, Pair.of(
            LocalDateTime.of(2022, 1, 1, 0, 0),
            LocalDateTime.of(2023, 12, 31, 23, 59, 59, 999_000_000)
        ));
        ranges.put(MV_MSG_EXC_REC_RECENT, Pair.of(
            LocalDateTime.of(2024, 1, 1, 0, 0),
            LocalDateTime.of(2024, 11, 4, 23, 59, 59, 999_000_000)
        ));
        ranges.put(MV_MSG_EXC_REC_CURRENT_DAY, Pair.of(
            LocalDateTime.of(2024, 11, 5, 0, 0),
            LocalDateTime.of(2024, 11, 5, 7, 30, 0, 0)
        ));

        return ranges;
    }
    
        private static Map<String, Pair<LocalDateTime, LocalDateTime>> getMvNames(LocalDateTime currentTime) {
        Map<String, Pair<LocalDateTime, LocalDateTime>> mvNamesWithDateRanges = new LinkedHashMap<>();
        
        Pair<LocalDateTime, LocalDateTime> dateRange = getUiDates();
        LocalDateTime fromUiDateTime = dateRange.getLeft();
        LocalDateTime toUiDateTime = dateRange.getRight();

        // Define the ranges based on the current time
        Map<String, Pair<LocalDateTime, LocalDateTime>> ranges = getDbMvRanges();

        // If dates are missing or "from" is after "to", return empty map
        if (fromUiDateTime == null || toUiDateTime == null || fromUiDateTime.isAfter(toUiDateTime)) {
            return mvNamesWithDateRanges;
        }

        // Iterate over the ranges and check if the UI dates fall within any of these ranges
        for (Map.Entry<String, Pair<LocalDateTime, LocalDateTime>> entry : ranges.entrySet()) {
            LocalDateTime rangeStart = entry.getValue().getLeft();
            LocalDateTime rangeEnd = entry.getValue().getRight();
            boolean fromInRange = !fromUiDateTime.isBefore(rangeStart) && !fromUiDateTime.isAfter(rangeEnd);
            boolean toInRange = !toUiDateTime.isBefore(rangeStart) && !toUiDateTime.isAfter(rangeEnd);

            if (fromInRange || toInRange) {
                mvNamesWithDateRanges.put(entry.getKey(), entry.getValue());
            }
        }

        // Check if "to" is greater than the current time
        if (toUiDateTime.isAfter(currentTime)) {
            mvNamesWithDateRanges.put(MV_MSG_EXC_REC_CURRENT_DAY, ranges.get(MV_MSG_EXC_REC_CURRENT_DAY));
        }

        // Check for specific entries and add "MV_MSG_EXC_REC_RECENT" if both are present
        boolean containsHistory = mvNamesWithDateRanges.containsKey(MV_MSG_EXC_REC_HISTORY);
        boolean containsCurrentDay = mvNamesWithDateRanges.containsKey(MV_MSG_EXC_REC_CURRENT_DAY);
        if (containsHistory && containsCurrentDay) {
            mvNamesWithDateRanges.put(MV_MSG_EXC_REC_RECENT, ranges.get(MV_MSG_EXC_REC_RECENT));
        }

        return mvNamesWithDateRanges;
    }
}

can you help me please to do the getMvNames to works as expected ?
as now it works with MV ranges

Thanks a lot

[Campbell Ritchie]

What sort of views? Are they views of a database?
Why are you giving that class a public constructor? If you are using the of() method it might be better not to allow the constructor to be used directly: give it private access.
Why are you using getClass() in the equals() method in a non‑final class? That will mean that instances of subtypes of Pair would not return true from equals(). That problem will disappear if Pair has a private constructor.
Unless L and R are always immutable, which LocalDateTime and String are, I think you should consider taking defensive copies of your fields.
Don't write your own null tests in equals(). Not when you can use Objects#equals() instead.
Why are you allowing nulls into your Pair in the first place? Have you considered a very early date or a very late date instead of the nulls? Have you considered using an Optional<LocalDateTime>? The ofNullable() method will allow you to create empty Optionals as appropriate, then you can use orElse(LocalDateTime.now()) if right is null, or something analogous if left is null.

[peter petros]

There are materialised views in db.
The code is just a demo class for explaining what is the issue.

[Tim Holloway]

Like Campbell, I'm confused. Are you saying that you're simply illustrating how you want to create your views using Java code as an example?

SQL has a very trivial operation for capturing a date range. It's the BETWEEN operator.

Also note the difference between an ordinary view and a materialized view. An ordinary view is created based on the current contents of the database. A materialized view is a snapshot view taken when the few was created and thereafter immutable. Any view that is based on a sliding value such as current date or time should not be a materialized view.

[Campbell Ritchie]

Please don't quote the whole of the preceding post; that adds nothing ot the discussion and is liable to removal without notice.

It sounds like something you should be doing on the database directly, as Tim said. The code you showed doesn't help us understand the problem.

[Liutauras Vilda]

@OP

You are failing to explain the problem and the task you have at hand at this very moment. Potentially you are trying to do too much at this stage.
Those cryptic names of tables, that later get transferred to code, i.e. variable names - don't help either to visualise problem in a head (at least mine).

Ignore cases 2, 3 and 4 to cover, unless you got case 1 working.
Can you explain, what is the relation between queries in DB you need to create (?) and the Java classes you created?