Filter by complete days

Summary

Let’s say you want your chart to look at a time range relative to the current date. There are various date filter operators you can use depending on how you want to filter the data. If you want your dates to be dependent on timestamps, you’d use a “relative date filter” operator. If you want to use full dates, you’d use an “absolute date filter” operator.

Consider this scenario: You refresh your chart on March 10th at 10:24 p.m. What’s the time range your chart would look at if you were to use a relative date filter operator or an absolute date filter operator?

Solution

Relative date filter operator

Our relative date filter operators are the “last N…” or “between N and M…” operators.

Continuing with the scenario above, what if our chart filters the data on the “last N days”? This subtracts N * 24 hours from the current timestamp. So if N is 1, the data we’d get back would be from the last 24 hours starting from the time we ran the query. If we ran the query at 10:24 p.m. on March 10th, then the time range of our chart would be from 10:24 p.m. March 9th to 10:24 p.m. March 10th. The chart would not include any data before 10:24 p.m. on March 9th or after 10:24p.m on March 10th.

Absolute date filter operators

Our absolute date filter operators are the “earlier than”, “later than”, “between”, and “between and including” operators. These filter your data by full days.

Continuing with the example above, if we were to use “between and including” to get data from the last day, the time range of our chart would be from 12:00 a.m. March 9th to 11:59 p.m. March 10th. Now all data from March 9th and 10th would be included in our chart.

You can also use relative date variables as the start and end dates for your date filter instead of absolute dates.

For example, if we want to look at the last full day, we’d use {TODAY.SUB(1,'day')} for the start date and {TODAY} as the end date for our date filter.