How to add new column by calculate difference & adding remaining difference to next column entry

Get Help
1

I want to show data visualization of time tracked.

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 or at 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, my data source have following columns : activity name, time started, time ended, duration, duration mins, tags, categories, comment

2

Hi there,

I believe you want to build a fact table (we have a similar example in our docs here).
You'll need to convert the "sleep for 8 hours at 11 PM" to 8 rows (one per hour) of "sleep at 11 PM", "sleep at 12 AM", and so on.

You can do this with some SQL work (example in postgres):

WITH date_series AS (
  SELECT *
  FROM GENERATE_SERIES('2025-07-01'::date, '2025-07-04'::date, '1 hour'::interval) hour
), -- One row for every hour
events AS (
  select '2025-07-01T23:00:00'::timestamp as start, 8 as hours 
)
SELECT * FROM sleep
LEFT JOIN date_series ON (hour >= start) AND (hour <= start + hours * interval '1 hour')

Then, you can do something like GROUP BY day, week, etc. with date_trunc

3

Because i lacked sql knowledge, i failed to understand you.

Following code worked for my use case. Which i got from discord sql server.

SELECT tag
  , date_trunc('day', offset_date) AS dt
   , COUNT(*) / 60 / 60::float AS hours
 -- , EXTRACT(EPOCH FROM (time_started::timestamp - time_ended::timestamp)) AS seconds_difference;
FROM
  (
    SELECT e.record_tags tag 
       ,   d.offset_date
    FROM  "public"."stt_records_20250621_124608_20250702123637" e
    CROSS JOIN 
          generate_series( e.time_started
                         , e.time_ended - '1 second'::interval
                         , '1 second'::interval
                         ) d(offset_date)
  ) t
GROUP BY tag
  , date_trunc('day', offset_date)
ORDER BY date_trunc('day', offset_date)
  , tag;

Time tracking , column split
just adding these keyword in post so other people can get help regarding this.