Timestamp Range Not Working vs. Date Format Range Working

Hopefully this makes sense. I have a document with two fields storing the same data, but one is in an epoch_millis timestamp format, and the other is in a readable format. The mapping for these two fields is:

"latest_datetime": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss },
"latest_timestamp": { "type": "date" },

Where the data inserted via JSON response is an already-formatted date, and a numeric epoch_millis timestamp. Some example data in those two fields for a document would be:

"latest_datetime":"2019-09-15 11:19:43",
"latest_timestamp":1568560783,

I'm attempting to do a range query (I've also tried a range filter) using ES datemath on the latest_timestamp field, but the date math always returns zero results. If I were to do the same datemath on the latest_datetime field, it works as expected. Does ES datemath not work on timestamp representations of dates? Or do I need to explicitly declare the format of the date field as "epoch_millis" for the datetime math to work?

The relevant part of the query:

{ "range":{ "latest_timestamp":{ "lte":"now", "gte":"now-60d" } } }

There are a number of different ways to fix this that involve other fields, or doing timestamp math separately, but I'd like to check for certain that this can't be done all in a single query instead. Thank you!

Note that the date format by default contains epoch_millis, which means the date you specified is somewhere near the epoch, and not in 2019...

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.