timestamp in milliseconds and date range - elasticsearch query string

Question

I have a timstamp in milliseconds, like 1645825932144

I'd like to make a date range query with elastic search query string for being able to get all records whom timestamp is in the last 24h: timestamp:[now-24h TO now]

This does not work as timestamp is in milliseconds and now produces strings like 2001-01-01 13:00:00

Is it possible to achieve this with a cast or something?

I read about range queries and date math, but did not find anything.

Answer 1

It's easy to compute the timestamp in milliseconds for now and now-24h so why not do it in your application logic and build the query out of those values?

For instance (in JS),

const now = new Date().getTime();
const now24h = now - 86400000;
const query = `timestamp:[${now24h} TO ${now}]`;

query would contain the following value:

timestamp:[1647785319578 TO 1647871719578]

UPDATE:

PS: I might have misunderstood the initial need, but I'm leaving the above answer as it might help others.

What you need to do in your case is to change your mapping so that your date field accepts both formats (normal date and timestamp), like this:

PUT your-index/_mapping
{
    "properties": {
      "timestamp": {
        "type": "date",
        "format": "date_optional_time||epoch_millis"
      }
    }
}

Then you'll be able to query like this by mix and matching timestamps and date math:

GET test/_search?q=timestamp:[now-24h TO 1645825932144]

and also like this:

GET test/_search?q=timestamp:[1645825932144 TO now]