# Change how Kibana interprets dates as datetimes **URL:** \ **Category:** Kibana\ **Tags:** transforms, ingest-pipeline\ **Created:** [February 15, 2023, 1:02pm UTC](https://discuss.elastic.co/t/change-how-kibana-interprets-dates-as-datetimes/325616 "2023-02-15T13:02:30Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![catrexis](https://avatars.discourse-cdn.com/v4/letter/c/53a042/32.png) [@catrexis](https://discuss.elastic.co/u/catrexis)\ **Post date:** [February 15, 2023, 1:02pm UTC](https://discuss.elastic.co/t/change-how-kibana-interprets-dates-as-datetimes/325616/1 "2023-02-15T13:02:30Z")
One of my indices uses a date (yyyy-MM-dd) instead of a datetime as timstamp-field. Kibana interprets that as a datetime, by setting the time to 01:00. As the document contains data of the whole day, I would like to set it to the end of the day, so something like 23:59:59. Is it possible to accomplish that via a setting in kibana? Or would you recommend to transform the field to a datetime when ingesting? Then, how would I add the end-of-day-time with an ingest pipeline? I could also use a transform since I'm already using one, if that's easier to do with transforms. Or should I better do the conversion before I send the document to ES? ---
**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\ **Post date:** [February 15, 2023, 2:13pm UTC](https://discuss.elastic.co/t/change-how-kibana-interprets-dates-as-datetimes/325616/2 "2023-02-15T14:13:52Z")
There are many ways to accomplish this. In my opinion you get the best out of elasticsearch search and analytic capabilities if your data is properly indexed, which means it is properly mapped. I therefore suggest to use a `date` field in the mappings. You can configure your format as part of mappings, by chance this documentation example uses the same pattern as you, please have a look [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html). ---
**Author:** ![catrexis](https://avatars.discourse-cdn.com/v4/letter/c/53a042/32.png) [@catrexis](https://discuss.elastic.co/u/catrexis)\ **Post date:** [February 15, 2023, 4:06pm UTC](https://discuss.elastic.co/t/change-how-kibana-interprets-dates-as-datetimes/325616/3 "2023-02-15T16:06:46Z")
The mapping is set to the correct date format (yyyy-MM-dd). When I search for the documents via the console, I get the correct format without a time. But the problem is, that Kibana automatically determines a time for these fields and sets it to 01:00:00. I would like to change that to be something like 23:59:59. So the question would be, if I can do this with a setting in Kibana that changes the auto-generation-behaviour, or, if not, how I can transform/re-injest a document from something like 2023-01-20 to 2023-01-20 23:59:59? ---
**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\ **Post date:** [February 15, 2023, 5:57pm UTC](https://discuss.elastic.co/t/change-how-kibana-interprets-dates-as-datetimes/325616/4 "2023-02-15T17:57:10Z")
Date field is tricky in all this big data technology Three thing to remember: when you sends date to elastic it converts it to UTC and saves it. then when you use kibana it convert it back to your LOCAL TIME Zone and show you. when you use sql query in kiabna dev tool it does not convert the time and shows you UTC(how ever it is saved) Now from here you can decide what you want to do. 02/16/23 12:00 AM in UTC is 02/15/23 06:00 PM in CST do the conversion and save date accordingly in Elastic and it will show up at your desire time. ---
**Author:** ![catrexis](https://avatars.discourse-cdn.com/v4/letter/c/53a042/32.png) [@catrexis](https://discuss.elastic.co/u/catrexis)\ **Post date:** [February 16, 2023, 6:55am UTC](https://discuss.elastic.co/t/change-how-kibana-interprets-dates-as-datetimes/325616/5 "2023-02-16T06:55:34Z")
I also thought about changing the zone, but that feels a bit hacky. Isn't there a way to modify the actual time when ingesting, something like a script that does mytimefield = mytimefield + 22h + 59m? I could set the time in my java client from where I send the documents, but that would not fix the documents, that are alredy in ES. ---
**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\ **Post date:** [February 16, 2023, 1:54pm UTC](https://discuss.elastic.co/t/change-how-kibana-interprets-dates-as-datetimes/325616/6 "2023-02-16T13:54:46Z")
if you want to change value in elastic then use update\_by\_query, can also use inline script to change value. I never changed date field though. ---
**Author:** ![catrexis](https://avatars.discourse-cdn.com/v4/letter/c/53a042/32.png) [@catrexis](https://discuss.elastic.co/u/catrexis)\ **Post date:** [February 16, 2023, 2:17pm UTC](https://discuss.elastic.co/t/change-how-kibana-interprets-dates-as-datetimes/325616/7 "2023-02-16T14:17:44Z")
I managed to do it with a scripted field that looks like: ```auto "runtime.end_of_day": { "type": "date", "format": "yyyy-MM-dd HH:mm:ss XXX", "script": { "source": """ ZonedDateTime dtIn = doc['aggregationDate'].value; ZonedDateTime dtOut = ZonedDateTime.of(dtIn.getYear(), dtIn.getMonthValue(), dtIn.getDayOfMonth(), 23, 59, 59, 0, ZoneId.of('Europe/Berlin')); emit(dtOut.toInstant().toEpochMilli()) """ } } ``` I first had some headaches with local time zones but it works now. Thank you all 🙂 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 16, 2023, 2:18pm UTC](https://discuss.elastic.co/t/change-how-kibana-interprets-dates-as-datetimes/325616/8 "2023-03-16T14:18:12Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.