How to split rows & add value to next row?

Grafana
1

Simplified example

Tag Date hours
sleep June 16, 2025, 11:00 PM 8

If i sleep at 23:00 till 7:00 on June 16 It is counted as i slept 8 hours on June 16 but thats wrong, it should count it as one hour splitting data by variable $CloseHour(00:00), Then adding remaining to next (June 17) column

Desired output

Tag Date hours
sleep June 16 1
sleep June 17 7

How can we achieve this? Is there any other solution?

Other unrelated information to problem. my data source have following columns : activity name, time started, time ended, duration, duration mins, tags, categories, comment

Data is in CSV format

I do not know any SQL but willing to invest time. So you can help me by telling me which topic to learn first to solve this problem.

2

You can try creating the query like this. I hope it will be helpful for you.

image
image951×396 20.8 KB

ORDER BY tag, date;

WITH split AS (
  SELECT
    tag,
    DATE(time_start) AS date1,
    LEAST(time_end, DATE_TRUNC('day', time_start) + INTERVAL '1 day') - time_start AS dur1,
    CASE 
      WHEN time_end > DATE_TRUNC('day', time_start) + INTERVAL '1 day' 
      THEN DATE(time_start) + INTERVAL '1 day' 
    END AS date2,
    CASE 
      WHEN time_end > DATE_TRUNC('day', time_start) + INTERVAL '1 day' 
      THEN time_end - (DATE_TRUNC('day', time_start) + INTERVAL '1 day') 
    END AS dur2
  FROM activities
)
SELECT tag, date1 AS date, ROUND(EXTRACT(EPOCH FROM dur1)/3600, 2) AS hours FROM split
UNION ALL
SELECT tag, date2 AS date, ROUND(EXTRACT(EPOCH FROM dur2)/3600, 2) AS hours FROM split WHERE date2 IS NOT NULL

image
image1718×362 42.2 KB