time series in mysql and grafana

example.sql

-- This assumes a `reservations` table with `start_date`, `end_date`, and `price` columns.

WITH RECURSIVE interval_in_seconds as (
  select
    floor(
      (
        UNIX_TIMESTAMP($__timeTo()) - UNIX_TIMESTAMP($__timeFrom())
      ) / 100
    ) as value
), time_series AS (
  SELECT
    $__timeFrom() as ts
  UNION
  ALL
  SELECT
    timestampadd(
      second,
      (
        select
          value
        from
          interval_in_seconds
      ),
      ts
    ) as ts
  FROM
    time_series
  WHERE
    timestampadd(
      second,
      (
        select
          value
        from
          interval_in_seconds
      ),
      ts
    ) <= $__timeTo()
)
SELECT
  t.ts AS time,
  r.price
FROM
  time_series t
  JOIN reservations r ON t.ts BETWEEN r.start_date AND r.end_date
ORDER BY
  t.ts

time series.sql

-- This is an example of viewing the time series data. In the example.sql file, we put it to use.
--
-- 500 in this example is the number of data points to generate
--
WITH RECURSIVE interval_in_seconds as (
  select
    floor(
      (
        UNIX_TIMESTAMP($__timeTo()) - UNIX_TIMESTAMP($__timeFrom())
      ) / 100
    ) as value
), time_series AS (
  SELECT
    $__timeFrom() as ts
  UNION
  ALL
  SELECT
    timestampadd(
      second,
      (
        select
          value
        from
          interval_in_seconds
      ),
      ts
    ) as ts
  FROM
    time_series
  WHERE
    timestampadd(
      second,
      (
        select
          value
        from
          interval_in_seconds
      ),
      ts
    ) <= $__timeTo()
)
select
  *,
  $__timeFrom() as grafana_start,
  $__timeTo() as grafana_end,
  (
    select
          value
        from
          interval_in_seconds
  ) as interval_in_seconds
from
  time_series;