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.

Val · Accepted Answer · 2022-03-21 15:53:33Z

1

+100

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]

edited Mar 21, 2022 at 15:53

answered Mar 21, 2022 at 14:09

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Guillaume Cisco Over a year ago

Thank you, but I'm only looking for query string here.

Val Over a year ago

How do you build your query string?

Guillaume Cisco Over a year ago

By writing it... This is only text. Your answer is absolutely not what I'm looking for. That's why it is a downvote.

Val Over a year ago

Sorry but your initial need wasn't clearly expressed and I might have misunderstood it. Now, I think I've got it, and updated my answer accordingly, please check it out again.

Guillaume Cisco Over a year ago

Thank you for your update. Yes that can be an option indeed. No way to do this directly from text?

|

Collectives™ on Stack Overflow

timestamp in milliseconds and date range - elasticsearch query string

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related