Change how Kibana interprets dates as datetimes

Elastic Stack Kibana
,
1

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?

2

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.

3

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?

4

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.

5

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.

6

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.

7

I managed to do it with a scripted field that looks like:

"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 :slight_smile:

8

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