Reputation: 21
I have an elastic search db deployed within an AWS VPC. It holds millions of records all with a timestamp added based on the unix datestamp (new Date().getTime()). I am trying to pull (1) record per time slot based on min/max hour and minute values.
Index Mapping: { timestamp: "date", ...rest of record }
Elastic Search Query:
let params = {
query: {
bool: {
must: [{
range: {
timestamp: {
gte: (unix date),
lte: (unix date)
}
}
},
{
script: {
script: {
source: "long datestamp = doc['timestamp'].value.getMillis(); " +
"Date dt = new java.util.Date(datestamp*1L); " +
"Calendar instance = Calendar.getInstance(); " +
"instance.setTime(dt); " +
"int hod = instance.get(Calendar.HOUR_OF_DAY); " +
"int tod = instance.get(Calendar.MINUTE); " +
"if (hod >= params.hourMin && hod <= params.hourMax && (hod === params.hourMin && tod >= params.timeMin || hod === params.hourMax && tod <= params.timeMax)) { return true; } else { return false }",
params: {
hourMin: 7,
hourMax: 8,
timeMin: 30,
timeMax: 10
}
}
}
}
]
}
},
from: 0,
size: 500
};
Issue: I often run into an error while searching indicating that
It shows up every 4~5th query generally speaking.
Question:
Is there a better way? I have poured over the elastic search docs regarding intervals, histograms, etc and came up with query above. Not sure if this is the most efficient method nor the most robust.
If this is a community accept approach to find records within an interval then how do I mitigate the errors I am encountering. Do I skip over a specific record or reformat the unix timestamp another way?
Appreciate your support ahead of time.
Upvotes: 0
Views: 673