> ## Documentation Index
> Fetch the complete documentation index at: https://honeydew.ai/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Time Spines

## Introduction

A time spine entity is a list of times (like consecutive dates), that has relations to time columns
of other entities.

The main use cases for a time spine are:

* Filling in missing data - for example when counting new users by day, a day without users should be counted as 0. But
  without a time spine, if counting on the new users table on a missing date, then it will just not appear.

* Providing a common time for different metrics - most metrics are based on time. When metrics are on different entities
  how to count them to the same day? Connect those entities to a time spine, and count them to the date in the spine.

A time spine in Honeydew is modeled as an entity, marked as a time spine.

<Tip>
  A time spine is always a [shared dimension](/advanced-modeling/shared-dimensions).
</Tip>

## Building a time spine

The time spine is an entity. It can be based on a table in the data warehouse, or on a custom SQL query, such as:

```sql theme={null}
-- Snowflake SQL for a per-day time spine spanning 2020-2030
SELECT
    DATEADD(DAY, row_number() over (order by seq), '2020-01-01'::date)::DATE AS DATE,
    DAYOFMONTH(DATE) as DAY_OF_MONTH,
    TO_VARCHAR(DATE, 'dy') as DAY_OF_WEEK,
    DAYOFWEEK(DATE) as DAY_OF_WEEK_INDEX,
    DAYOFYEAR(DATE) as DAY_OF_YEAR,
    DATE_TRUNC('month', DATE) as MONTH,
    MONTHNAME(DATE) as MONTH_NAME,
    MONTH(DATE) as MONTH_NUM,
    DATE_TRUNC('quarter', DATE) as QUARTER,
    'Q' || QUARTER(DATE) as QUARTER_OF_YEAR,
    DATE_TRUNC('week', DATE) as WEEK,
    WEEKOFYEAR(DATE) as WEEK_OF_YEAR,
    YEAR(DATE) as YEAR,
FROM (SELECT SEQ4() as seq FROM TABLE(GENERATOR(ROWCOUNT=>365*10)))
```

<Note>
  1. The time spine entity should be identified by setting the `is_time_spine` metadata tag in its YAML configuration.
  2. Multiple time spines are not supported.
</Note>

<Tip>
  Always prefer creating time spines as tables and not as custom SQL entities.

  Snowflake does not cache queries that use table generators.
  Using a time spine as custom SQL without [materialization](/performance/entity-caching) will disable caching for queries
  that involve the time spine.
</Tip>

The time spine should have the granularity column as key - in a per-day spine, the date is the key.
Make sure to set the `timegrain` attribute for the granularity column, to enforce that granularity.

## Connecting a time spine

time spines can be connected to entities via a relationship.

By convention, when connecting an entity to a time spines, the following relationship properties should be set:

* Key: Per-date granularity
* Join type: `many-to-one` (a time spine is always a shared dimension)
* Join type: `right` (a time spine is always filling missing dates)
* Cross filtering direction: `one-to-many` (a time spine can filter other entities, but is never filtered by them)

<Tip>
  When building a time spine from the UI, the default configuration is set automatically.
</Tip>

### Default date field

When an entity is connected using a [relationship](/modeling/relations) to a time spine, the connecting field
is called the "default date field".

For example, if `orders` is connected to a time spine on the `orders.order_date = date.date` field, then
`order_date` is the default date field for all metrics acting on `orders`.

Metrics within an entity that includes a default date field will use it for [time metrics](/advanced-modeling/time-metrics), utilizing the associated time spine.

In particular,

1. For time metrics, Honeydew will populate any missing dates in the default date field.
   For example, consider a user counting `TIME_METRIC(orders.count)` by `date.month` (while `date` is a time spine and `orders.order_date` is
   the default date field used to connect `orders` with `date`). If there are no valid `order_date` rows (i.e. no orders)
   between March 1st and March 31st, then Honeydew will fill March as a month with zero orders.
2. When using time offset syntax (`TIME_METRIC(orders.count, offset => '1 month')`) the offset will be relative to
   the default date field.
3. When using period-to-date syntax the accumulation will be relative to the default field.

<Tip>
  The Honeydew convention for a per-day time spine is to call the entity `date`
</Tip>