Date Format UTC in Elastic Search Query

[rakesh1 (rakesh kumar)]

Hi ,

Section of my elastic search query -
"bool": {
"must_not": {
"range": {
"lastsynctoes_date": {
"gte": "now/d",
"lte": "now/d"
}
}
},
...
}
This works well, however, picks up the server date time (UTC) which is ahead of PST by 8 hours (approx). Now, I need to re-format this date suitable to the specific time zone.

One suggestion, that I had received was to convert the query string, i.e. currently in json format prior to sending the ES server and replace the values of "now/d" , with the required date time format, which I did. The resulting json appeared as below.
"bool": {
"must_not": {
"range": {
"lastsynctoes_date": {
"gte": "2018/01/02 18:30:00",
"lte": "2018/01/03 18:29:59"
}
}
},
...
}
However received error as I check the same in fiddler. parse_exception","reason":"failed to parse date field [2018/01/02 18:30:00] with format [strict_date_optional_time]"}]
,"type":"search_phase_execution_exception", ...

Is there any way, that I can resolve the issue. either by modification of the json query directly, or by modification of the string to a specific format, to be able to send the query in a required time zone format.
In other words, can I format "gte": "now/d", to a specific date time zone.

Thanks,
Rakesh.

[DavidTurner]

You can give an explicit timezone in a range query using the time_zone parameter, which converts now/d to the appropriate local midnight. You can use IANA timezone names such as America/Los_Angeles as well as explicit offsets, to account for daylight saving changes.

You can also convert the query string to an absolute time as you described, but you must format it correctly. The format expected, according to the error message, is strict_date_optional_time which is in the documented list and described in full here. Note that the separator in the date should be a - and not a / and there should be a T between date and time rather than a space.

Note that if you write your queries as {"gte": "2018/01/02 18:30:00","lte": "2018/01/03 18:29:59"} then you will miss documents with times strictly between 18:29:59 and 18:30:00 which could happen if there are any fractional seconds. Prefer "lt":"2018/01/03 18:30:00" if possible.

[rakesh1 (rakesh kumar)]

Hi David,

Thanks for the response. Between, I have actually had the error corrected, by capturing the json, being sent to the elastic search server and replacing the string of lte & gte with their equivalent UTC dates.
Yes, you are correct in saying that the date separator should be - rather than a / and there should be a T between date and time rather than a space.
The equivalent C# code is here (for any future references - if any) -
var startDateUTC = DateTime.Today;
var gteDate = startDateUTC.ToUniversalTime().ToString("O");
var lteDate = startDateUTC.AddDays(1).AddSeconds(-1).ToUniversalTime().ToString("O");
searchAndUpdateCriteria = searchAndUpdateCriteria.Replace("__nowgte", gteDate);
searchAndUpdateCriteria = searchAndUpdateCriteria.Replace("__nowlte", lteDate);

Json query -
Json –
"must_not": {
"range": {
"lastsynctoes_date": {
"gte": "__nowgte",
"lte": "__nowlte"
}
}
},

Final json resulting to -
"must_not": {
"range": {
"lastsynctoes_date": {
"gte": "2018-01-04T18:30:00.0000000Z",
"lte": "2018-01-05T18:29:59.0000000Z"
}
}
}

Thanks,
Rakesh.