Grafana v9.4.3 Table Panel: Unable to perform operation on date column field

Hi,

I am rendering a table panel view with raw data metric using elastic-search data source and it successfully display the table.
The requirement is to perform some operation on one column field of the table (returnTime) which is having a date format something like “2023-11-20 10:00:00” and I need a new column like “Delay” which basically does a simple math

now() - toDateTime(returnTime)

To achieve this, I was using Transform -> Add field from calculation -> Binary Operation feature of grafana and it gives me two dropdowns to select the field or number. But I guess I am unable to use “toDateTime” and “now” functions in the dropdowns.

Can anyone please help me on this. Any help would be highly appreciated.

Thanks
Lakshay

for more control over these types of additional columns and calculations I would go with infinity plugin that works nicely with elastic search then using jsonata you can do pretty much anything you want.

1 Like

@yosiasz thankyou for the reply, I have a query, can we use floor/ceil basically Math class methods too ?

Yes via jsonata

Numeric functions · JSONata.

1 Like

@yosiasz Sorry I have a query, JSONata does not seems to be a plugin how it will work with the Infinity plugin which you mentioned ? So you mean Infinity plugin can’t support floor, ceil methods it seems ?

Because as mentioned earlier I need to control the column data in Grafana itself by modifying/adding a column by transforming a date of format “2023-11-20 10:00:00” to hours by using below formulae in Grafana itself


Logic is something like below which I need in Grafana with some Plugin since currently Table plugin doesn't handle it:

floor(now() - toDateTime(returnTime) / 3600)

Jsonata is a feature of infinity plugin

1 Like

okay, I didnt see anything related to JSONata in doc of infinity plugin, can you please share me the infinity plugin which you are mentioning.

https://grafana.github.io/grafana-infinity-datasource/docs/uql

Jsonata is its own query language that infinity implements.

Search for jsonata in this forum there are tons of examples

1 Like

Thanks a lot @yosiasz I will surely go through this.

@yosiasz I was able to now bring the delay in hours but I facing one issue I need to convert the delay hours to some alias based on a calculation like if it is b/w

* Between 0 and 24 display as '0-24h'
* Between 24 and 48 display as '24-48h'
* Between 48 and 72 display as '48-72h'
* Greater than 72 as '>72h'

For the above I am using below query but it does not seems to be working.

parse-json
.....
| jsonata "delayHours"=("delayHours" < 24) ? "0-24h" : "24-48h"

Can you please help me on this?

Its working now, did some adjustments. Thanks :slight_smile:

1 Like