Skip to main content
DocumentationLibraryReference
On this page

Kater SQL Functions Derive

KATER__PERIODS_SINCE

How many whole periods there are from the since date to this date.

Family: Derive

Syntax 

KATER__PERIODS_SINCE(field, since, unit)

Arguments

ArgumentDescription
fieldThe date to count to, such as an order date.
sinceThe date to count from, such as the date a customer signed up. A date: a field, a grain of its date, such as KATER__MONTH(order_date), or a fixed day, such as ‘2024-02-01’::DATE.
unitThe length of one period. One of 'day', 'week', 'month', 'quarter', 'year'.

Returns

A whole number. It is negative where the date is before since. The field menu writes @format number(0) beside it.

Usage notes

  • Both dates count from the start of their period, so from 31 January to 1 February is 1 month.
  • A cohort’s age is this count, such as months since the first order.

Example

This Sandbox query reads the sample shop’s orders. The table shows the rows that Kater gave.

SELECT
  orders.order_id AS "Order ID",
  customers.signup_date AS "Signup Date",
  orders.order_date AS "Order Date",
  KATER__PERIODS_SINCE(orders.order_date, customers.signup_date, 'month') AS "Months Since Signup"
FROM kater_dataset.orders AS orders
JOIN kater_dataset.customers AS customers
  ON orders.customer_id = customers.customer_id
WHERE
  KATER__IS_NOT_NULL(customers.signup_date)
ORDER BY
  1
LIMIT 3
Order IDSignup DateOrder DateMonths Since Signup
O000002024-04-122025-05-2613
O000012024-08-172025-11-2815
O000022025-01-122025-07-256

Compiles to 

DATE_DIFF(
  'MONTH',
  CAST(DATE_TRUNC('MONTH', customers.signup_date) AS DATE),
  CAST(DATE_TRUNC('MONTH', orders.order_date) AS DATE)
)
DATEDIFF(
  MONTH,
  CAST(DATE_TRUNC('MONTH', customers.signup_date) AS DATE),
  CAST(DATE_TRUNC('MONTH', orders.order_date) AS DATE)
)

See also