Sometimes it can be useful to filter using complete date buckets (days, weeks, months, etc.), as opposed to the last 24 hours, 7 days, etc.
Chartio’s Relative Date (ex.”last N days”) and Between Dates (ex. “between N and M days before now”) filter options work by subtracting days from the current timestamp.
For example, filtering using the “last N days” filter for the last day will show the results for the last 24 hours.
If you’d like to include full days instead of using timestamps, you can either use Relative Date Variables as part of your filters in Interactive Mode or you can edit your query in SQL Mode.
If you’d like to filter using full dates in Interactive mode, simply use the “between and including” filter option and utilize our Relative Date Variables as the start and end dates for your date filter instead of absolute dates.
For example, to filter for the full last 2 days, we would use the following as the start and end dates for our filter:
If you prefer to adjust your date filters in SQL Mode, you can make a few simple changes to the auto-generated query to use the current date instead of the current timestamp.
For example, if we wanted to filter a date column for the last 2 days, we would use the “last N days” filter in Chartio to produce this query:
WHERE ("date_column" BETWEEN NOW() - INTERVAL '2 DAY' AND NOW())
WHERE ("date_column" BETWEEN CURRENT_DATE - INTERVAL '2 DAY' AND CURRENT_DATE)
This will filter the query to the last 2 complete days as opposed to the last 48 hours.
This example uses PostgreSQL, but for other databases, replace the NOW() and CURRENT_DATE functions with the equivalent timestamp and current date variables.