Sum of miliseconds Kibana

[gribeiro (Guilherme)]

Hi,

I have in my SQL Server a DateTime2 Field which represents how long a task took to be completed.
In a simple way, the value is StartDateTimeOfTask - GetDate(), that gives me the result in seconds, and it would be stored like this: 1900-01-01 00:00:05.0966667 --> the task took 5 seconds to complete.

I've tried to store in my ES 7.7 this data ( as DateTime or Integer) so I could sum the time by task, tried to format as Duration and Number 00:00:00, but without success

I've found this topic JSON Input: Converting Minutes to Minutes and Seconds which almost get what I want, but the result is not what I was expecting:

So what I am trying to do is to insert this kind of data in a certain way that I will be able to Sum the seconds/miliseconds of a group of tasks

is this possible in anyway?

Thank you very much,
G

[markov00 (Marco Vettorello)]

I don't know how SQL Server work but I can't understand why StartDateTimeOfTask - GetDate() will give you back a date instead of the difference in millis between the end date and the start date.
Having this as a simple integer it will be much easier to store in ES and do all the calculations you need.
Could you try to convert StartDateTimeOfTask and GetDate() to UTCmillis?
Is something like this available in SQL Server: DATEDIFF(MILLISECOND, begin, end)? this should return an integer value of milliseconds elapsed from the begin to the end

[gribeiro (Guilherme)]

I created another column in MSSQL Table, saving the milliseconds as Integer, using DATEDIFF, and when sending it to ESK is working as expected.

So, in order to display in a more readable way, I created a scripted field, to display minutes:seconds.milliseconds

For the purpose of displaying document per document, it works perfectly, but I cannot Sum this scripted field, because is now a date type. Is there a way to create a sum to visualize this number as hh:mm:ss.SSSS?
Otherwise I have the sum of thousands of milliseconds, like this:

this sum represents 1 minutes - 6 seconds - 814 milliseconds

Thanks

[markov00 (Marco Vettorello)]

Hi, you don't need to use a scripted field for that, you can just specify the format as Number and use the following NumberJS to format as time 00:00:00
This will keep your field as number to allow all the numeric aggregation you need, but you are then formatting the number as a time

[markov00 (Marco Vettorello)]

This unfortunately only play nicely with seconds