Hour_minute date format in range queries

[Adam_M]

Hello,

I'm preparing bus timetable and I got a problem with query.
My mapping (below) has hour field in hour_minute format.

{
  "stops": {
    "properties": {
      "dayType": {
        "type": "string"
      },
      "stopNumber": {
        "type": "string"
      },
      "hour": {
        "format": "hour_minute",
        "type": "date"
      },
      "stopDetails": {
        "type": "string"
      },
      "location": {
        "type": "geo_point"
      },
      "lineNumber": {
        "type": "string"
      }
    }
  }
}

I would like to have the times starting from now and then sort them ascending but querying from now doesn't work. I know it's a problem with timestamp that stays behind now so I started passing time value like 22:34 and it works but returns documents only to 23:59.

I would really love my query to return documents, say from 22:34 to 2:15 but I don't know how to do it.

{
  "query": {
    "constant_score": {
      "filter": {
        "bool": {
          "should": [
            {
              "bool": {
                "must": [
                  {
                    "range": {
                      "hour": {
                        "gte": "now",
                        "format": "HH:mm",
                        "include_lower": true,
                        "include_upper": true
                      }
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

[cbuescher (Christoph)]

Hi,

I think the problem you are has to do with the ways dates are stored in Elasticsearch internally and the way "now" is resolved. To start with the first, dates are converted to UTC and stored as a long number representing milliseconds-since-the-epoch. So if you index a date like "03:00" it will be stored as the long value "10800000" internally.

On the other hand, "now" gets resolved to the current UTC time (the format doesn't really affect that, it is just enabling parsing the date string), so it will be much larger than the dates you are storing. If you want to store only the hours/minutes of your bus depatures, you need to somehow subtract the time when "today" starts from that value. I tried some date math like "now-now/d" which I thought would do just that (subtract "now" rounded to the current day from "now"), but that doesn't seem to work. But something along those lines. If that doesn't work you might need to try to figure out the milliseconds-since-start-of-day somehow in your application.