Querying documents for the past 10 minutes is not working


(Ryan Ermita) #1

i have a query like this to query documents within the past 10 minutes, but it returns documents outside the 10 minutes range.
{
"_source": ["timestamp"],
"query": {
"range":{
"timestamp":{
"gt": "now-10m"
}
}
}
}

This is a sample of my timestamp format:
"timestamp": "2017-10-10T09:59:26.601417"


(David Pilato) #2

Just to make sure: timestamp is a date field in the mapping, right? Any chance it’s a text instead?

What was the server time when you ran the query? Note that elasticsearch assumes by default UTC time.


(Ryan Ermita) #3

I checked my mappings and it shows that the date is in date type.

"timestamp": {
 "type": "date"
},

I changed the format of my timestamp from my django app.

['timestamp'] = datetime.now().isoformat()[:-3] + 'Z'

which give the result of

"timestamp": "2017-10-10T09:59:26.601Z"

I tried again the range query but it returns the same issue.


(Azharuddin) #4

Thanks for the information.


(David Pilato) #5

What was the server time when you ran the query?


(Ryan Ermita) #6

I run a query with a timestamp of
"timestamp": "2017-10-10T16:38:14.296Z"
but i still get the other documents that shouldnt be there.

and i checked the server time, and it is in UTC which is the same with ES.

I tried to add format in my query, but still no success.

{
	"_source": ["timestamp"],
	"query" : { 
        "range" : {
        	"timestamp":{
        		"gt": "now-5m",
        		"format": "strict_date_time||yyyy-MM-dd'T'HH:mm:ss.SSSZ"
        	}
        }
    }
}

(Ryan Ermita) #7

just an update. I tried to run the query via curl in my local computer and in our server to validated both date and time.
both curl request had the same result..


(David Pilato) #8

I ran that on my laptop at 11:49 (I'm GMT+2 TZ):

DELETE dpi
PUT dpi
{
  "mappings": {
    "doc": {
      "properties": {
        "timestamp": {
          "type": "date"
        }
      }
    }
  }
}
PUT dpi/doc/1
{
  "timestamp": "2017-10-10T11:45:00+02:00"
}
PUT dpi/doc/2
{
  "timestamp": "2017-10-10T11:30:00+02:00"
}
POST dpi/_refresh
GET dpi/_search
{
  "query": {
    "range": {
      "timestamp": {
        "gt": "now-5m"
      }
    }
  }
}

It gave me the right results:

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 1,
    "hits": [
      {
        "_index": "dpi",
        "_type": "doc",
        "_id": "1",
        "_score": 1,
        "_source": {
          "timestamp": "2017-10-10T11:45:00+02:00"
        }
      }
    ]
  }
}

(Ryan Ermita) #9

Hi @dadoonet.

its working on my end now. So here's what we did, for other people's reference.

I set my timestamp(local timezone) in my django app:
timestamp = datetime.now(pytz.timezone("Asia/Manila")).isoformat()

which results with this timestamp in my elasticsearch document:
"timestamp": "2017-10-10T18:34:21.786623+08:00"

I also change this query

{
"_source": ["timestamp"],
"query": {
"range": {
"timestamp": {
"gte": "now-10m"
}
}
},
"sort": {"timestamp": {"order": "desc"}}
}

with this one:

{
"_source": ["timestamp"],
"query": {
"range": {
"timestamp": {
"gte": "now-10m/m",
"lte": "now/m"
}
}
},
"sort": {"timestamp": {"order": "desc"}}
}

and it works. I understand now how date format affects my query but it keeps me wondering why I need to add the rounding up option and "lte": "now/m" in my query?


(Ryan Ermita) #10

Thank you @dadoonet! :+1:


(system) #11

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