# Visualization of scripted field using relative time but sometimes field is null **URL:** \ **Category:** Kibana\ **Created:** [April 6, 2020, 8:20pm UTC](https://discuss.elastic.co/t/visualization-of-scripted-field-using-relative-time-but-sometimes-field-is-null/226772 "2020-04-06T20:20:42Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Goishin](https://avatars.discourse-cdn.com/v4/letter/g/46a35a/32.png) [@Goishin](https://discuss.elastic.co/u/Goishin)\ **Post date:** [April 6, 2020, 8:20pm UTC](https://discuss.elastic.co/t/visualization-of-scripted-field-using-relative-time-but-sometimes-field-is-null/226772/1 "2020-04-06T20:20:43Z")
I'm trying to create a metric that lists how long a development pipeline has been broken for. I compute this field in the script I use to upload my documents with. The field is marked as relative time in my index. What I'd really like for this to do is when a pipeline breaks, we mark the time in the field "results.failing\_since". Then, when the pipeline isn't broken, meaning all my tests pass, then the field is "null". When I add this to a metric, it always skips over the "null" documents. Then I created a script to use, that when the field is "null", I'll just pass a string saying "pipeline not broken". However, that fails because apparently the metric visualization is expecting a number. And if I just have it return a 0, instead, then I get an exception: class\_cast\_exception: org.elasticsearch.script.JodaCompatibleZonedDateTime cannot be cast to java.lang.Number" When I try to do this in a data table, then the timestamp won't be rendered as relative time. AND, the field is missing on some pipelines, too. So, I have this state where a field can or cannot be present, can or cannot be null, and should be rendered as relative time. I've tried something similar to this on a datatable. But this can still result in an exception, and the datatable won't format this as relative time (if it isn't busy throwing exceptions, that is): ```auto { "script": "if (doc.containsKey('results.failing_since') && doc['results.failing_since'].size() != 0 && doc['results.failing_since'].value != null) { return doc['results.failing_since'].value;} else { return 'Not failing.';}" } ``` How do I get this data visualized!!! ---
**Author:** ![markov00](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/markov00/32/33316_2.png) [@markov00](https://discuss.elastic.co/u/markov00)\ **Post date:** [April 14, 2020, 8:04am UTC](https://discuss.elastic.co/t/visualization-of-scripted-field-using-relative-time-but-sometimes-field-is-null/226772/2 "2020-04-14T08:04:50Z")
Hi @Goishin could you send me an example of what the visualization should looks like? it's just a metric visualization with the sum of how many seconds/minutes/hours the pipelines have been broken? How your documents are structured? because I don't understand your concerns on the skipped `null` documents since they should not count as part of the "failing" pipeline. In any case: the exception seems related to one possible root cause. The scripted field is of type Number but you are returning the value of `results.failing_since` that is a Date value. You can cast it to Unix timestamp with something like `doc['@timestamp'].value.toInstant().toEpochMilli();` If you then format your scripted field as `Duration` it should return a nice formatted value on the visualization ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 12, 2020, 8:05am UTC](https://discuss.elastic.co/t/visualization-of-scripted-field-using-relative-time-but-sometimes-field-is-null/226772/3 "2020-05-12T08:05:03Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.