Time Spines

​

Introduction

A time spine entity is a list of dates, that has relations to date 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.

​

Building a time spine

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

-- 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)))

The Honeydew convention for a per-day time spine is to call it date.

The time spine should have the granularity column as key - in a per-day spine, the date is the key.