# Date Format UTC in Elastic Search Query **URL:** \ **Category:** Elasticsearch\ **Created:** [January 3, 2018, 10:49am UTC](https://discuss.elastic.co/t/date-format-utc-in-elastic-search-query/113903 "2018-01-03T10:49:06Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![rakesh1](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@rakesh1](https://discuss.elastic.co/u/rakesh1)\ **Post date:** [January 3, 2018, 10:49am UTC](https://discuss.elastic.co/t/date-format-utc-in-elastic-search-query/113903/1 "2018-01-03T10:49:06Z")
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. ---
**Author:** ![DavidTurner](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/davidturner/32/22453_2.png) [@DavidTurner](https://discuss.elastic.co/u/DavidTurner)\ **Post date:** [January 5, 2018, 10:40am UTC](https://discuss.elastic.co/t/date-format-utc-in-elastic-search-query/113903/2 "2018-01-05T10:40:23Z")
You can give an explicit timezone in a range query using [the `time_zone` parameter](https://www.elastic.co/guide/en/elasticsearch/reference/6.1/query-dsl-range-query.html#_time_zone_in_range_queries), which converts `now/d` to the appropriate _local_ midnight. You can use [IANA timezone names](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones) 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](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html#built-in-date-formats) and [described in full here](http://www.joda.org/joda-time/apidocs/org/joda/time/format/ISODateTimeFormat.html#dateOptionalTimeParser--). 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. ---
**Author:** ![rakesh1](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@rakesh1](https://discuss.elastic.co/u/rakesh1)\ **Post date:** [January 5, 2018, 11:09am UTC](https://discuss.elastic.co/t/date-format-utc-in-elastic-search-query/113903/3 "2018-01-05T11:09:03Z")
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. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 2, 2018, 11:09am UTC](https://discuss.elastic.co/t/date-format-utc-in-elastic-search-query/113903/4 "2018-02-02T11:09:07Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.