Select to display hours and minutes.

[Rafael Brauns]

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.

select ACTIVITY, to_char(START_TIME, 'DD.MM.YYYY-HH24:MI:SS'), to_char(END_TIME, 'DD.MM.YYYY-HH24:MI:SS'), CAST(FLOAT(BYTES)/1024/1024/1024 as DEC(8,0)), SUCCESSFUL, TIMESTAMPDIFF(4, CHAR(endt_time-start_time)) as TOTAL_RUN_TIME from ACTIVITY_SUMMARY where start_time>=(current_timestamp-7 days) and activity='FULL_DBBACKUP' order by start_time DESC

ACTIVITY: FULL_DBBACKUP
Unnamed[2]: 06.02.2023-15:00:26
Unnamed[3]: 06.02.2023-15:17:55
Unnamed[4]: 77
SUCCESSFUL: YES
TOTAL_RUN_TIME: 17

and it would be nice something like this

ACTIVITY: FULL_DBBACKUP
Unnamed[2]: 06.02.2023-15:00:26
Unnamed[3]: 06.02.2023-15:17:55
Unnamed[4]: 77
SUCCESSFUL: YES
TOTAL_RUN_TIME: 00:17

[Jeremy Rickard]

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