Working With Dates

There are a few things to note when working with Date Filter and Date Range Filter in Holistics.

Global Time Zone#

In Holistics, All schedules (email schedules, imports, data transforms, etc...) are based on your browser's timezone.

On the other hand, Relative date/time are calculated using the Global Time Zone that is set in the Settings page. This is the organization-wide timezone.

Relative Date Syntax#

When setting default values for date filters, we support flexible relative dates.

  • yesterday
  • today
  • 2 weeks ago
  • 1 month ago
  • 30 days ago
  • 2 weeks from now
  • last week Tuesday
  • 3rd Wednesday in November
  • last month end (the last day of the previous month)
  • last month begin (the first day of the previous month)

We also support "begin" or "end" to indicate the beginning/end of each week/month/year.

Different aggregation intervals in time-series#

In a time-series report, you might want to group data by different time intervals such as daily, weekly, monthly, quarterly. For this purpose, you can create a time_period filter and use it in a DATE_TRUNC() (or equivalent) function. We will demonstrate how this combination works.

Create time_period filter#

We will create a Dropdown Filter named time_period (1) with the following Manual Entries (2):

day,Daily
week,Weekly
month,Monthly
quarter,Quarterly

Finalize the filter by input default value 'week' (3) and checking Hide 'All' (4), then click on the Preview (5) button to see the result:

Use the filter in a DATE_TRUNC() function#

The query using PostgreSQL is:

with data as (
select
current_date + series.a as report_date
from generate_series(1, 60, 1) as series(a)
)
select
date_trunc({{ time_period }}, report_date) as report_time,
count(1) as counting
from data
group by 1
order by 1 desc

If you use BigQuery's Standardsql:

#standardsql
with data as (
select
report_date
from unnest(generate_date_array(current_date, date_add(current_date, interval 60 day), interval 1 day)) as report_date
)
select
date_trunc(report_date, {{ time_period|noquote }}) as report_time
, count(1) as counting
from data
group by 1
order by 1 desc

When you select a time period value, the query will be modified so that report_time is truncated to day, week, month, year... level.

The approach above works best in more complicated cases in SQL (accumulate SUM, growth rate, average over days, months, weeks...), while Date Drill can only handle simple SUM.

Date Format#

We currently support the following date format:

  • Day
  • Week
  • Month
  • Quarter
  • Year