Retrieving Number of Days in Month

[shgpc]

Hi,

Been working with the enhanced table, trying to build a table with a computed column and I've been hitting a wall trying to evaluate one part that left me with some questions on whether it's doable.

I've a dataset of records. Each one includes amongst other things a string entry for an organisation, a number of seconds downtime as an integer, and a datetime of when this entry was made.
I'm trying to calculate for each organisation, the percentage uptime based on the total possible uptime. So aggregated for each organisation, number of hours in the given month less hours downtime for that organisation, all divided by the number of hours in a given month.
Currently, I'm assuming a month has 720 hours so the calculation is straightforward, but I've been trying to find a way to determine exactly how many hours were in the month in question for each entry, and I think I should be able to do this using the kibana-enhanced-table functions and in-built variables, but I'm struggling.

I've tried to determine a way to find what month it is given a date, but I can't see any way to extract that information from the timestamp using the functions available.
So as another approach, I think it should be possible to return the number of hours in a date range base on the documentation, so I should be able to use this to dynamically give me the number of hours in the month an entry occurred in, but I'm having trouble figuring out the syntax of how to use the timeRange variable in a computed column. I've not managed to get it returning anything other than an error or an empty result.
I also tried to simply take a substring of the date column to do this, but I think I've the syntax wrong here too, Enhanced Table error throws an error if I try for example substring(col2,1,5) saying t.substring is not a function, invalid expression in computed column: substring(col2,1,5).

If someone could offer some insight into how to use the timeRange variables with a datetime column to calculate something like this, or why I might be having issues with the substring function, I'd appreciate it.

Regards,
Seán

[fbaligand]

Hi,

First, you have issue with substring function, because you try to apply substring function on a integer value, not a string value. So javascript says you that substring function does not exist on a integer object.

Then, if you want to retrieve month from a date ; given that your date input is an integer that is the number of milliseconds since Epoch, well I'm sorry
but at this time, there is no method available in computed column formula to build a "date" object from "number of milliseconds since Epoch".

At best you can compute "approximate" month number, doing so:
date / 1000 / 60 / 60 / 24 / 30 % 12

It is based on fact that, as average, there are 30 days per month.
And yes, I know this is not great.

[shgpc]

Hi,

Thanks for your input! I figured that would be the answer but it's worth asking. I appreciate it.

-Seán