A date can group its rows by a period, such as its month. It can also give one part of itself, such as its day of the week. Kater has a function for each grain and each part, and it turns each one into your warehouse’s SQL. A grain keeps the date field. A part is a new field.

How it works

• A grain keeps the field. KATER__DATE, KATER__WEEK, KATER__MONTH, KATER__QUARTER and KATER__YEAR give the first day of each period, as a date on every warehouse. So the grain is in the data. KATER__HOUR, KATER__MINUTE, KATER__SECOND and KATER__MILLISECOND do the same for a timestamp, and give a timestamp. KATER__MONTH(shop.order_date) is still the order_date field, by month, with the label Order Date. The Timeframe menu writes grains.
• A part is a new field. KATER__DAY_OF_WEEK, KATER__DAY_OF_WEEK_INDEX, KATER__DAY_OF_MONTH, KATER__DAY_OF_QUARTER, KATER__DAY_OF_YEAR, KATER__WEEK_OF_YEAR, KATER__MONTH_NUM, KATER__MONTH_NAME, KATER__QUARTER_OF_YEAR, KATER__HOUR_OF_DAY and KATER__MINUTE_OF_HOUR each give a different value. Declare each one with its own name. The Derive menu writes parts.
• A plain date has no time of day. The compiler refuses an hour, minute, second or millisecond grain on a date, and an hour or minute part.

Order O00002 of the sample shop is on 2025-07-25, a Friday, at 13:56:

Grains

• A week starts on Monday in the sample shop. 2025-07-21 is the Monday before the order. The warehouse decides the first day of the week.
• A date grain of a timestamp is a date. KATER__WEEK(ordered_at) gives the date 2025-07-21, with no time.
• The shop records times to the minute. So the second and millisecond grains give the same time as the minute grain.
• A DATE_TRUNC written by hand keeps the field too. The Timeframe menu reopens it as a grain. But on DuckDB, DATE_TRUNC('month', shop.order_date) gives a timestamp, such as 2026-07-01T00:00:00. KATER__MONTH gives a date on every warehouse.

Parts

The Derive menu writes the format. plain shows a number as written, so 2026 shows as 2026, not 2,026. A name has no format, because a format does not change text. The menu also writes the name and the label, such as day_of_week__order_date and “Order Date (Day of Week)”.

Examples

Orders by month

How many orders did the shop get each month?

1
SELECT
2
  KATER__MONTH(shop.order_date),
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  KATER__MONTH(shop.order_date)
7
ORDER BY
8
  1

The first 6 of 21 rows, January 2025 to September 2026. Each row is the first day of its month. The column is still the Order Date field, so it keeps the field’s label.

Change the grain in place

How does the same count look week by week? Change the month to a week. The grain changes around the original date.

1
SELECT
2
  KATER__WEEK(shop.order_date),
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  KATER__WEEK(shop.order_date)
7
ORDER BY
8
  1

The first 6 of 92 rows. The first week starts on Monday 2024-12-30, before the first order. It holds the orders of 1 to 5 January 2025. The week reads each order’s own date. It never truncates a date that is already a month.

Orders by day of the week

On which day of the week do people order most? The orders dataset declares the day’s name with the Derive menu’s Day of Week:

1
  DIMENSION('day_name__order_date', KATER__DAY_OF_WEEK(orders.order_date)) AS "Order Date Day Name",

1
SELECT
2
  shop.day_name__order_date,
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.day_name__order_date
7
ORDER BY
8
  1

Monday has the most orders, 466. The names sort as text, from Fri to Wed, not in calendar order.

Days in calendar order

How do you sort the days from Sunday to Saturday? Add the day’s number, and sort by it.

1
SELECT
2
  shop.day_name__order_date,
3
  DIMENSION('day_of_week_index__order_date', KATER__DAY_OF_WEEK_INDEX(shop.order_date)) AS "Order Date (Day of Week Index)",
4
  shop.count_distinct__order_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.day_name__order_date,
8
  2
9
ORDER BY
10
  2

On DuckDB, the index starts at 0 on Sunday, so Sunday sorts first. A week from KATER__WEEK starts on Monday, so the two do not agree.

Months in calendar order

How many orders fall in each month of the year, from January to December?

1
SELECT
2
  shop.month_name__order_date,
3
  DIMENSION('month_num__order_date', KATER__MONTH_NUM(shop.order_date)) AS "Order Date (Month Number)",
4
  shop.count_distinct__order_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.month_name__order_date,
8
  2
9
ORDER BY
10
  2

The first 8 of 12 rows. A month name puts all the years together: Jan counts January 2025 and January 2026. October, November and December count about half as many, 149, 154 and 124, because the data stops in September 2026.

Orders by hour of day

At what hour do people order? Ordered At is a timestamp, so it has an hour. This line in semantic.shop declares the hour, as Derive and Hour of Day write it:

1
  -- @format plain
2
  DIMENSION('hour_of_day__ordered_at', KATER__HOUR_OF_DAY(o.ordered_at)) AS "Ordered At (Hour of Day)",

1
SELECT
2
  shop.hour_of_day__ordered_at,
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.hour_of_day__ordered_at
7
ORDER BY
8
  1

The first 8 of 24 rows. Hour 0 counts the orders from 00:00 to 00:59. The sample shop’s orders spread across all 24 hours.

Two grains of one date

Can one query show the year and the month of Order Date side by side? Both grains keep the order_date field, so one of them needs its own name.

1
SELECT
2
  KATER__YEAR(shop.order_date),
3
  KATER__MONTH(shop.order_date),
4
  shop.count_distinct__order_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  1,
8
  2

The compiler refuses it: “Duplicate public field identifier ‘order_date’; declare a uniquely named variant”. Declare the year as a new field:

1
SELECT
2
  DIMENSION('order_year', KATER__YEAR(shop.order_date)) AS "Order Year",
3
  KATER__MONTH(shop.order_date),
4
  shop.count_distinct__order_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  1,
8
  2
9
ORDER BY
10
  2

The first 3 of 21 rows. Order Year is the first day of each year, because a date grain is a date.

The field list writes this for you. In a query by month, open Order Date and click Year. Kater declares DIMENSION('order_date_year', KATER__YEAR(shop.order_date)) AS "Order Date (Year)", and Year is ticked under Order Date.

Periods since another date

KATER__PERIODS_SINCE counts whole periods from one date to another, such as the months since a customer’s first order. Cohort charts use it for each cohort’s age.

1
KATER__PERIODS_SINCE(date, since, 'unit')

Both dates count from the start of their period, so every warehouse counts the same boundaries. This query counts three orders from the first day of their month:

1
SELECT
2
  shop.order_id,
3
  shop.order_date,
4
  shop.order_month,
5
  DIMENSION('days_since__order_month', KATER__PERIODS_SINCE(shop.order_date, shop.order_month, 'day')) AS "Days",
6
  DIMENSION('weeks_since__order_month', KATER__PERIODS_SINCE(shop.order_date, shop.order_month, 'week')) AS "Weeks",
7
  DIMENSION('months_since__order_month', KATER__PERIODS_SINCE(shop.order_date, shop.order_month, 'month')) AS "Months"
8
FROM workspace.shop AS shop
9
WHERE shop.order_id IN ('O00002', 'O00000', 'O00001')

For O00000, 25 days are about three and a half weeks, but the count is 4 weeks. 2025-05-01 is in the week that starts on Monday 28 April, and 2025-05-26 is a Monday. The Derive menu offers Periods Since on a date only when there is another date to count from. It writes the format number(0).

In the editor

• Timeframe in a shared model. Right-click a date in the field list. Under Make a new field from it, select Timeframe, then Week, Month, Quarter or Year. Kater adds a new field at once, such as week__order_date, “Order Date (Week)”.
• Timeframes in the field list. In a query, a date in the field list opens to Raw, Week, Month, Quarter and Year, and a timestamp also has Hour and Day. Click one to add the date by that timeframe, and click it again to take it out. A field the model already publishes by month has Raw, Quarter and Year. See Queries.
• Timeframe in a query. Right-click the date, select Timeframe, then Year, Quarter, Month, Week or Day. Kater changes the grain in place.
• Derive. Right-click a date and select Derive. Each part opens the New dimension dialog. Turn it into names the part in plain words, such as Day of week or Month number. Select Add to the model in a shared model, or Create dimension in a query.

Rules

• A plain date has no time of day. KATER__HOUR(shop.order_date) is refused: “KATER__HOUR: shop.order_date is a date, so it has no time of day. Use one of a date’s own, such as KATER__DATE or KATER__DAY_OF_WEEK.”
• A monthly value has no daily detail. Order Month in the sample shop is already a month. A week or a day of it cannot bring back the dates inside the month. To see weeks, change the grain on Order Date itself.
• A SELECT holds a field once. Two grains of one date keep the same field. Declare all but one under their own names.
• A name sorts as text. Sort a day or a month name by its index or its number.
• A date filter is not a grain. To keep the last 3 months, use a date filter such as KATER__IS_WITHIN_LAST. See Filters and parameters.
• A period comparison is a custom calculation. To compare this month with last month, see Period comparisons.

Common errors