SQL Helpers

SQL Helpers take away some of the tedious and verbose parts of writing SQL.

Timestamp Helpers

Instead of the standard SQL date_trunc function using date_trunc(${interval}, timestamp), you can use [timestamp:${interval}] where valid ${interval} is one of:

  • microseconds
  • milliseconds
  • second
  • minute
  • hour
  • day
  • week
  • month
  • quarter
  • year
  • decade
  • century
  • millennium

Example

Let's assume you have a table with a timestamp that you want to aggregate monthly. Without the helper, you'd be writing

SELECT date_trunc('month', joined_date::timestamp), count(id)
FROM customers
GROUP BY 1;

With the helper, you can simply write:

SELECT [joined_date:month], count(id)
FROM customers
GROUP BY 1;

Dashboard helper: aggregation

The special helper aggregation makes the aggregation interval user configurable: one amongst year, quarter, month, week and day. The default is quarter if no aggregation is picked. An example:

SELECT [joined_date:aggregation], count(id)
FROM customers
GROUP BY 1;

Dashboard helper: daterange

The special helper daterange makes the time axis user configurable: between relative periods this_week, this_month, last_30days, and absolute periods.

Example

Let's assume you have a table with a column joined_date that you want to apply the date range filter. Your query will look like the following (with an additional aggregation thrown in):

SELECT [joined_date:aggregation], count(id)
FROM customers
WHERE [joined_date=daterange]
GROUP BY 1;

The above query will get rewritten as:

SELECT date_trunc('quarter', joined_date), count(id)
FROM customers
WHERE FIXME
GROUP BY 1;

To filter just on the start [daterange_start] or the end [daterange_end] of the date range, use the following:

SELECT [joined_date:aggregation], count(id)
FROM customers
WHERE joined_date < [daterange_end]

Possible Values

If you rather hardcode the date range filter, instead of making it user configurable, the following are supported:

  • last_week
  • this_week
  • this_month
  • last_30days
  • last_quarter
  • this_quarter
  • last_year
  • this_year

These date ranges are relative, and will always reflect the time they were run at.