column chart of the range between 2 times

[spisiakmi]

Hi, I have 

StartTime,EndTime
"2023-12-05 05:30:00.0000000","2023-12-05 08:00:00.0000000"
"2023-12-05 08:00:00.0000000","2023-12-05 09:30:00.0000000"
"2023-12-05 10:28:00.0000000","2023-12-05 13:30:00.0000000"

I need to visualize a column chart, with 3 columns (in this case) with height 1 (y axis). The width of the first column is between "2023-12-05 05:30:00.0000000","2023-12-05 08:00:00.0000000", second one between "2023-12-05 08:00:00.0000000","2023-12-05 09:30:00.0000000", the third between "2023-12-05 10:28:00.0000000","2023-12-05 13:30:00.0000000". The x axis should be the time. Attached example. Any idea, please?

[spisiakmi]

I have 1 question. The solution shows the time range in restricted _time. It is possible to expand it into/show in selected time range, which is defined in the time range picker? To the range addinfo.info_max_time, addinfo.info_min_time?

[ITWhisperer]

I am not sure how your solutions works since you are not setting _time when row=3, and it is not clear what "restricted" _time is, nor what your expected result should look like.

[spisiakmi]

1. there is a time range picker object on the dashboard. If I select any range, e.G. the whole day 05.12.2023, this time range I would like to have on x-axis in area chart.
2. in this case, 

| eval _time=case(row=0,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=1,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=2,strptime(EndTime,"%Y-%m-%d %H:%M:%S"),row=3,strptime(EndTime,"%Y-%m-%d %H:%M:%S"))
| eval value=case(row=0,0,row=1,1,row=2,1,row=3,0)

the time range of the x-axis in area chart is from the first StartTime (05:30) ... last EndTime (13:30).

[spisiakmi]

This is, what I want to achieve. 3.jpg. Time range from the time range picker. In this case the day 05.12.2023.

[spisiakmi]

And here is the solution

| eval row=mvrange(0,6)
| mvexpand row
| addinfo
| eval _time=case(row=0,info_min_time,row=1,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=2,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=3,strptime(EndTime,"%Y-%m-%d %H:%M:%S"),row=4,strptime(EndTime,"%Y-%m-%d %H:%M:%S"),row=5,info_max_time)
| eval value=case(row=0,0,row=1,0,row=2,1,row=3,1,row=4,0,row=5,0)
| table _time, value

[spisiakmi]

Attached result 2.jpg

[ITWhisperer]

Try something like this

| makeresults format=csv data="StartTime,EndTime
2023-12-05 05:30:00.0000000,2023-12-05 08:00:00.0000000
2023-12-05 08:00:00.0000000,2023-12-05 09:30:00.0000000
2023-12-05 10:28:00.0000000,2023-12-05 13:30:00.0000000"
| eval row=mvrange(0,4)
| mvexpand row
| eval _time=case(row=0,strptime(StartTime,"%F %T.%6N"),row=1,strptime(StartTime,"%F %T.%6N"),row=2,strptime(EndTime,"%F %T.%6N"),row=3,strptime(EndTime,"%F %T.%6N"))
| eval value=case(row=0,0,row=1,1,row=2,1,row=3,0)
| table _time value

Then use an area chart viz

[spisiakmi]

Hi ITWhisperer, thx for sharing it. Unfortunately, if I run your code I receive no results.

[ITWhisperer]

As you can see from my runanywhere example, it does work. How have you actually implemented my suggestion? What results do you get? What do your actual events look like?

[spisiakmi]

From your code I recived this:
"_time",value
,0
,1
,1
,0

[spisiakmi]

But, if I use this code on the content, which I mentioned in the main describtion, I receive these results (see attch 1.jpg). And this is quiet good for me, except the triangel step. Any idea, how to fix it?
| eval row=mvrange(0,4)
| mvexpand row
| eval _time=case(row=0,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=1,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=2,strptime(EndTime,"%Y-%m-%d %H:%M:%S"),row=3,strptime(EndTime,"%Y-%m-%d %H:%M:%S"))
| eval value=case(row=0,1,row=1,1,row=2,1,row=3,0)
| table _time value

[ITWhisperer]

Looks like you haven't evaluated _time

| eval _time=case(row=0,strptime(StartTime,"%F %T.%6N"),row=1,strptime(StartTime,"%F %T.%6N"),row=2,strptime(EndTime,"%F %T.%6N"),row=3,strptime(EndTime,"%F %T.%6N"))

[spisiakmi · Accepted solution]

Hi ITWhisperer, I fixed it. Thank you very very much for your help,

with this, it is working properly (look attached 2.jpg):

| sort StartTime
| eval row=mvrange(0,4)
| mvexpand row
| eval _time=case(row=0,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=1,strptime(StartTime,"%Y-%m-%d %H:%M:%S"),row=2,strptime(EndTime,"%Y-%m-%d %H:%M:%S"),row=3,strptime(EndTime,"%Y-%m-%d %H:%M:%S"))
| eval value=case(row=0,1,row=1,1,row=2,1,row=2,0) ´here is the difference
| table _time value