Showing time graph with 15min intervals instead of continuous every minutes & sec

Let’s say you have log / event data with continuous time. Something like the following:

 eventtype      ts
 abc            2013-08-03 02:20:11
 dxy            2013-08-03 06:18:12   
 jjj            2013-08-03 09:56:00   
 klx            2013-08-03 11:41:12   

But you want to trend event counts by 15 min intervals. Let’s for now assume rounding down. For example, 11:23 --> it gets rounded to 11:15. Or 11:18 --> get rounded up to 11:15 as well.

Step1 - calculation for 15min interval

  • We want to create a calculated column which will give us the time in rounded down 15min interval:

    from_unixtime(cast(unix_timestamp([ts]) - (unix_timestamp([ts])%900) as INT), "yyyy-MM-dd HH:mm")

    This will allow us to get event counts by that new dimension

Step 2: Trend line

  • We can turn this into trend line, just make sure to sort the dimension column in ascending order:

    image

Step 3: Time display format

  • We can then use display format to show the hour & minutes for the day
    image

  • Also removing the x-axis label will give our visual a clean look

Step 4: Running total

And we can do further manipulations using our 15min interval graph. We can get the running total by applying the out of the Analytic Function to the record count as shown here:

image

In the dialog box, you can keep the default settings and click ok:

image

The final graph shows a nice running total with 15min intervals on the x-axis