Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data with timestamps for start and end. I want to calculate the minutes between the timestamps and distribute per hour.
Anybody that can help?
My data: | |
Start_timestamp | End_timestamp |
04/02/2022 12.55.00 | 04/02/2022 15.25.00 |
Desired output: | |
DateTime | Minutes |
04/02/2022 12 | 5 |
04/02/2022 13 | 60 |
04/02/2022 14 | 60 |
04/02/2022 15 | 25 |
for date you can use expression in final table
if(IsNull(Start_timestampdate),Peek(Start_timestampdate),Start_timestampdate) as Start_timestampdate
after for timestamp use
Timestamp(Start_timestampdate+time,'DD/MM/YYYY hh.mm.ss')
use Interval()
Interval(Timestamp#(End_timestamp,'dd/mm/yyyy hh.mm.ss')-Timestamp#(Start_timestamp,'dd/mm/yyyy hh.mm.ss'),'mm') as minutes_diff
@vinieme12 thanks for the reply and sorry for my bad english.
My problem is not calculating the minutes between the timestamps.
I want to calculate the minutes between the timestamps per hour. For instans at hour 15 there are 25 minutes between the timestamps. And at hour 14 there are 60 minutes between the timestamps and so on.
You would likely need to use Load ... While and IterNo() to create a record for each hour ((IterNo()-1)/24) should be the means to add hours to the starttime. For each hour, you'll need to calculate the lesser of Endtime minus that hour, or 60, which you should be able to do using RangeMin().
Anybody that can help ?
please try it once hope it is helpful...
thanks @Padma123 , it looks promising 🙂
I also need the date in the field DateTime, because sometimes the start timestamp and the end timestamp have different dates.
Do you also have a solution for that ?
for date you can use expression in final table
if(IsNull(Start_timestampdate),Peek(Start_timestampdate),Start_timestampdate) as Start_timestampdate
after for timestamp use
Timestamp(Start_timestampdate+time,'DD/MM/YYYY hh.mm.ss')
@Padma123 , awesome, thank you:)