Holistics AQL learning Lesson 0005

AQLearn 1.4 / Function

date_trunc

Source lesson: AQLearn 1.4: Function: date_trunc. Tangible win: bucket a date or datetime dimension into year, quarter, month, day.

Why this matters for your mission: trend dashboards live on time buckets. Customers ask "by month" or "by quarter" all the time. You will write this every day.

Core idea

date_trunc() is a scalar function that truncates a date or datetime field to a given time granularity. It returns the start of that bucket (for example, the first day of the month).

date_trunc(model.field, 'datetime_part')

Shorthand pipe form, more common in practice:

model.field | datetime_part()

year()

One bucket per year. Example: 2024-01-01.

quarter()

One bucket per calendar quarter. Example: 2024-04-01.

month()

One bucket per calendar month. Example: 2024-03-01.

day()

One bucket per day. Example: 2024-03-12.

Other valid parts: 'hour', 'minute', 'week', and more. See the date_trunc reference.

Dataset diagram

Same AQLearn ecommerce dataset as before. This lesson focuses on the orders.created_at field.

N : 1 N : 1 N : 1 N : 1 N : 1 countries codetext, pk nametext continent_nametext cities idnumber, pk nametext country_codetext, fk users idnumber, pk nametext gendertext agenumber sign_up_atdatetime city_idnumber, fk orders this lesson idnumber, pk statustext created_atdatetime delivery_attemptsnumber discountnumber user_idnumber, fk order_items order_idnumber, fk product_idnumber, fk quantitynumber products idnumber, pk nametext pricenumber category_idnumber, fk merchant_idnumber, fk
Highlighted field: orders.created_at. We will truncate it to year, quarter, month, and day.
primary key foreign key

Same field, four granularities

Starting from this explore:

explore {
  measures {
    count_of_orders: orders | count(orders.id),
  }
  dimensions {
    time: orders.created_at,
  }
}

Swap the time dimension to get different buckets.

GoalDimension expressionExample bucket
By yearorders.created_at | year()2024-01-01
By quarterorders.created_at | quarter()2024-04-01
By monthorders.created_at | month()2024-03-01
By dayorders.created_at | day()2024-03-12

Yearly count of orders

explore {
  measures {
    count_of_orders: orders | count(orders.id),
  }
  dimensions {
    time: orders.created_at | year(),
  }
}

Average discount per quarter

explore {
  measures {
    avg_order_discount: orders | avg(orders.discount),
  }
  dimensions {
    time: orders.created_at | quarter(),
  }
}
Customer-safe explanation: "We bucket the order date into the granularity the chart needs, then aggregate inside each bucket."

Retrieval practice

1. Which function buckets a datetime into one row per calendar month?
2. date_trunc(orders.created_at, 'month') and orders.created_at | month() are...
3. Customer wants "average discount per quarter". Which dimension expression?
4. date_trunc() returns...

Do in AQLearn

  1. Open AQLearn date_trunc.
  2. Set the time dimension to orders.created_at | year() and run. Expect 4 rows (2020 to 2023).
  3. Add a measure avg_order_discount: orders | avg(orders.discount),.
  4. Switch the time dimension to orders.created_at | quarter(). Run.
  5. Read the result by quarter and say out loud "average order discount in this quarter".

Next local lesson

Next: filters. How to narrow the rows that feed your measures using explore filters {}, datetime literals, and operators.