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.


Next Generation SQL Data Analysis Write SQL, visualize data, and share your results. Onboarding forward-thinking teams now:

No spam, ever! Unsubscribe any time. Learn more about the product.