Forum: Building VoltDB Applications

Post: How to do slidding window?

How to do slidding window?
javadevmtl
Aug 17, 2016
Hi how can we check the amount of times an "account" has been accessed in the last 5 minutes?

Is it just a view with a between where clause now - 5 minutes?
bballard
Aug 18, 2016
You can query your table directly, which may be fast if you have an index on account and the timestamp. There are more ways than one to write the expression, but WHERE ... timestamp_column > DATEADD(MINUTE, -1, NOW) is probably the easiest.

If you want to use a VIEW, for example if you have many thousands of rows in the last 5 minutes, so the above query is not as fast as you'd like, you need to define the VIEW on non-relative values. So for example, you could do a "minutely" summary using GROUP BY account, TRUNCATE(MINUTE,timestamp_column). Then you can query this VIEW where the (truncated) time column is in the last 5 minutes. This will usually be faster, but it does have a flutter. For example, if you are querying the database every second and displaying this in a dashboard, you would see something like a sawtooth rather than a steady line as the "last five minutes" because the current minute would only be partially complete most of the time. It would be smoother to instead use a time range so you don't see the current minute and look at the past 5 complete minutes. You could group by seconds of course, and query for the last 300 seconds and this effect would not be very apparent.