On Monday, 13 February 2023 at 21:50:09 UTC+13, Rafael Brauns wrote:
> Hi,
>
> can anyone help-me with this select?
> I want to display the minutes and hours, because sometimes the process takes over 1 hour to complete.
[snipped]
Hi Rafael,
You have already calculated the time in minutes. You just need a straight division for the hours part (i.e. TOTAL_RUN_TIME / 60), and the modulo for the remaining minutes (i.e. MOD(TOTAL_RUN_TIME, 60)). However, since you cannot reference TIMESTAMPDIFF in the same level you will need to repeat the TIMESTAMPDIFF calculation, or you could nest SELECTs e.g. SELECT ACTIVITY, (TOTAL_RUN_TIME / 60) || ':' || MOD(TOTAL_RUN_TIME, 60)) FROM (SELECT ACTIVITY, TIMESTAMPDIFF(4, CHAR(endt_time-start_time)) as TOTAL_RUN_TIME from ACTIVITY_SUMMARY etc...). I hope that gives you a rough idea.
Jeremy