You can use the Pivot node to achieve such an intermediate table. But as you suspected, there are simpler ways. Like in most instances when dealing with time, it's easiest to convert your time values to numerical values first. There are a lot of ways for that, depending on the type of your input values and the expected result. After that, you can use a GroupBy node to calculate the difference. Just use the ID as a grouping column and range as the aggregation method.
attached you will find a small example workflow. I would not pivot your table if it is not necessary. I used a group by to select the user and the Time Difference node to calculate the difference between two consequent rows (option previous)
I sorted the subtable in the group loop start by time.
hi Marlin, sorry for this novice question, issit you mean i need to use "string to date" > "groupby" node to do that? issit groupby can calculate the time difference?
hi Iris, sorry for extra question, i got around 5 million of records (rows), and the looping 1 by 1 seem too slow, is there got any faster method?
A GroupBy (alone) can not achieve the same result as Iris' suggestion. I understood your question in the sense that you wanted the total range per group, not individual differences per row. If that is what you want, yes, it can calculate differences between time values, either in days or in milliseconds (the aggregation methods are called "Date range").
The Group Loop Iris used does not iterate over single rows, but over groups. So you'll probably end up with only a few ten thousand iterations. And you are right that loops have a performance overhead, but in this case the alternatives would have to iterate more often instead. Also, even on a desktop machine, a few ten thousand rows should be done in a matter of hours, even more so with such a simple calculation. Just be sure to store the results in case you accidently disconnect a node.