Date and DateTime Dimensions

Use date and date_time dimensions for time-based grouping and filtering.

Overview

• date: Date without time (e.g. order date, birth date). Format: YYYY-MM-DD.
• date_time: Timestamp with time (e.g. created_at, event time). Format: YYYY-MM-DD HH:MM
  .

Define them as dimensions and set grains to advertise how callers can group and filter.

Grains

Grains are the granularities you allow for a date or date_time dimension.

- type: dimension
  name: Order Date
  data_type: date
  grains:
    - day
    - week
    - month
    - quarter
    - year
  expression:
    sql: order_date

Available grains:

For date_time, you can include raw, second, minute, and hour when you need sub-day precision.

Date Dimension Example

- type: dimension
  name: Order Date
  description: Date when order was placed
  data_type: date
  grains:
    - day
    - week
    - month
    - quarter
    - year
  expression:
    sql: order_date

DateTime Dimension Example

- type: dimension
  name: Created At
  description: When the record was created
  data_type: date_time
  grains:
    - raw
    - hour
    - day
    - week
    - month
  expression:
    sql: created_at

When to Use date vs date_time

• date: Order date, ship date, birth date, any dimension where time-of-day does not matter.
• date_time: Created at, updated at, event time, any dimension where hour/minute/second can be used for grouping or filtering.

Grouping and Formatting

• A grain is applied at request time with a truncate decorator on the projection; the SQL you get back carries DATE_TRUNC('month', ...) in the datasource’s dialect. See Projections.
• Set a field format such as date:short when your client should print the value a particular way; it is metadata, not SQL.

Best Practices

1. Set grains: only include grains you need; they tell callers what grouping makes sense.
2. Prefer date when time is irrelevant: Simpler and more efficient.
3. Use date_time for events: when callers need hour or minute level.

Next Steps

• Dimensions
• Data types
• Field format