home-launcher/activities/www/chart.sql

SET ":user" = COALESCE(:user, $user, '');
SET ":date" = COALESCE(:date, $date, CURRENT_TIMESTAMP);
SET ":start_of_day" = (SELECT datetime(datetime(:date, 'localtime'), 'start of day'));
SET ":end_of_day" = (SELECT datetime(:start_of_day, '+1 days'));

SET ":total_time" = (WITH delta AS (SELECT julianday(COALESCE((
  SELECT min(lag.ts, datetime(:end_of_day, 'utc'))
  FROM activities lag
  WHERE lag.ts > a.ts AND lag.activity <> a.activity
  ORDER BY lag.ts ASC
  LIMIT 1
), min(CURRENT_TIMESTAMP, datetime(:end_of_day, 'utc')))) - julianday(a.ts) AS delta
, activity
FROM activities a
WHERE user = :user
AND datetime(datetime(ts, 'localtime'), 'start of day') = :start_of_day)
SELECT printf("%.0f", sum(delta)*60*24) AS minutes --, activity
FROM delta
WHERE activity = 'Eating'
GROUP BY delta.activity);

SELECT 'chart' AS component
--, '' AS title
, :total_time || ' min.' AS title
, TRUE AS time
, 'area' AS type
, 1 AS ymax
, 0 AS ymin
, 1 AS ystep
, '100' AS height
-- TODO: fix color to series
--
--  SELECT DISTINCT color(activity) FROM activities ORDER BY activity;
, 'azure' AS color
, 'green' AS color
, 'red' AS color
;
SET ":any" = EXISTS (SELECT * FROM activities
    WHERE user = :user
    AND datetime(ts, 'localtime')
    BETWEEN :start_of_day AND :end_of_day
    LIMIT 1
);
SELECT NULL AS x, NULL AS y, 'Unknown' AS series WHERE :any;
SELECT NULL AS x, NULL AS y, 'Eating' AS series WHERE :any;
SELECT NULL AS x, NULL AS y, 'Not eating' AS series WHERE :any;
SET ":morning_offset" = '08:00:00.000';
SET ":evening_offset" = '03:00:00.000';
WITH plot AS (
    SELECT ROW_NUMBER() OVER (ORDER BY ts) AS row
    , ts
    , activity
    FROM (
        SELECT ts, activity FROM activities
        WHERE user = :user
        AND datetime(ts, 'localtime') BETWEEN :start_of_day AND :end_of_day
        UNION
        SELECT datetime(:start_of_day, 'utc'), (
            SELECT activity FROM activities
            WHERE user = :user AND datetime(ts, 'localtime') <= :start_of_day
            AND :any
            ORDER BY ts DESC
            LIMIT 1
        )
        UNION
        SELECT datetime(:end_of_day, 'utc'), (
            SELECT activity FROM activities
            WHERE user = :user AND datetime(ts, 'localtime') <= :end_of_day
            AND :any
            ORDER BY ts DESC
            LIMIT 1
        )
    )
)
SELECT datetime(:start_of_day, '+'||:morning_offset) AS x, 1 AS y, (
    SELECT activity FROM activities
    WHERE user = :user AND datetime(ts, 'localtime') <= datetime(:start_of_day, '+'||:morning_offset)
    AND :any
    ORDER BY ts DESC
    LIMIT 1
) AS series
UNION
SELECT datetime(:end_of_day, '-'||:evening_offset) AS x, 1 AS y, (
    SELECT activity FROM activities
    WHERE user = :user AND datetime(ts, 'localtime') <= datetime(:end_of_day, '-'||:evening_offset)
    AND :any
    ORDER BY ts DESC
    LIMIT 1
) AS series
UNION
SELECT datetime(x, 'localtime') AS x, CASE value WHEN 0 THEN NULL ELSE value END AS y, series FROM (
    SELECT datetime(next.ts, '-00:00:01.000') AS x, 0 AS value, next.activity AS series
    FROM plot, plot next
    WHERE plot.row = next.row - 1
    AND plot.activity <> next.activity
    UNION
    SELECT ts AS x, 1 AS value, activity AS series
    FROM plot
    UNION
    SELECT datetime(next.ts, '-00:00:01.000') AS x, 1 AS value, plot.activity AS series
    FROM plot, plot next
    WHERE plot.row = next.row - 1
    AND plot.activity <> next.activity
    UNION
    SELECT next.ts AS x, 0 AS value, plot.activity AS series
    FROM plot, plot next
    WHERE plot.row = next.row - 1
    AND plot.activity <> next.activity
) q
WHERE
    datetime(datetime(x, 'localtime'), '-'||:morning_offset) >= :start_of_day
AND
    datetime(datetime(x, 'localtime'), '+'||:evening_offset) <= :end_of_day
;