Working with Dates

The following sections provide tips about features related to date and time.

Timezone

There are 2 timezone settings in Holistics:

Global Timezone (in Admin > Settings > General) This is the timezone we use to resolve the relative date (in our relative date syntax).
User Timezone: (in User > Settings) This is the timezone we use for scheduling recurring work (email schedule, import, data transform, etc). An example is the Schedule column in the Email Schedules page.

Relative Date Syntax

When setting default values for date filters, or email schedules date's values, 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

You can add "begin" or "end" at the end to indicate the beginning/end of each week/month/year:

last month begin
last month end
2 months ago begin
last week begin
2 weeks ago end

Time-series Report with `time_period` Filter

In a time-series report, you might want to group data by different time intervals: such as daily, weekly, monthly. You can create a time_period filter to reuse a single report to support all these summaries.

Here is an example setup:

Filter Setup

Report Setup

1) Add the filter to report

2) Use a database date converter function together with the time_period filter. The example query is in PostgreSQL, so the function date_trunc (doc) is used.

with data as (
  select
    current_date + series.a as date,
    series.a as value
  from generate_series(1, 60, 1) as series(a)
)

select 
  date_trunc({{ time_period }}, date) as time,
  sum(value) as total
from data
group by 1
order by 1 desc

3) Output

Optional Date Format

In the output, even though we choose week, the date format is still day. You can set the date format to By 'time_period' filter. Holistics will automatically format the date according to the selected value of time_period filter.

We currently support 3 filter values: day, week, month, and your filter variable must be named time_period. Otherwise, Holistics will default to date format auto.

Please note the column value is formatted appropriately according to the selected filter value week.

{{ section.section }}