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,
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
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
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
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
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
This unfortunately only play nicely with seconds
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.