JodGig Metabase Notes

Custom Date Table for Rhesa

When creating date filers in Metabase, it will only allow you to filter by a date column from that table.

When joining tables like slot_user table and credits table, you will need to input the same dates for the slot_user.created_at and credits.created_at columns.

We create a custom date table to get around this limitation so that the UI can use that table to filter all the dates.

-- Step 1: Create the table with an id and date attributes
CREATE TABLE metabase_dates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date_value DATE UNIQUE,
    year INT,
    month INT,
    day INT,
    day_of_week INT,
    day_name VARCHAR(10),
    month_name VARCHAR(10),
    is_weekend BOOLEAN
);

-- Step 2: Insert dates into the table using the numbers table method with proper aliases
INSERT INTO metabase_dates (date_value)
SELECT DATE_ADD('2015-01-01', INTERVAL (units.n + tens.n + hundreds.n + thousands.n + ten_thousands.n) DAY) AS date_value
FROM
    -- Units place (0-9)
    (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS units
CROSS JOIN
    -- Tens place (0-90)
    (SELECT 0 AS n UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40
     UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90) AS tens
CROSS JOIN
    -- Hundreds place (0-900)
    (SELECT 0 AS n UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT 400
     UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900) AS hundreds
CROSS JOIN
    -- Thousands place (0-9000)
    (SELECT 0 AS n UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL SELECT 3000 UNION ALL SELECT 4000
     UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000) AS thousands
CROSS JOIN
    -- Ten-thousands place (0-30000)
    (SELECT 0 AS n UNION ALL SELECT 10000 UNION ALL SELECT 20000 UNION ALL SELECT 30000) AS ten_thousands
WHERE
    (units.n + tens.n + hundreds.n + thousands.n + ten_thousands.n) <= DATEDIFF('2114-12-31', '2015-01-01')
ORDER BY date_value;
-- Query OK, 36524 rows affected (0.35 sec)
-- Records: 36524  Duplicates: 0  Warnings: 0

-- Step 3: Update the table to populate additional date attributes
UPDATE metabase_dates
SET
  year = YEAR(date_value),
  month = MONTH(date_value),
  day = DAY(date_value),
  day_of_week = DAYOFWEEK(date_value),
  day_name = DAYNAME(date_value),
  month_name = MONTHNAME(date_value),
  is_weekend = CASE WHEN DAYOFWEEK(date_value) IN (1,7) THEN TRUE ELSE FALSE END;
-- Query OK, 36524 rows affected (0.66 sec)
-- Rows matched: 36524  Changed: 36524  Warnings: 0

Custom Date Table for Rhesa​

Custom Date Table for Rhesa