Calculating interval between startdate/time and enddate/time

[DB6000]

Hi All,

Could anyone assist me with getting  the interval time between jobs with a start date and time and end date and time?

I have a custom format dd/mm/yyyy hh:mm

for example:

jobid	StartTime	EndTime
4	01/02/2020 09:56	01/02/2020 10:08
9	01/02/2020 10:48	01/02/2020 10:56
13	01/02/2020 10:59	01/02/2020 11:12
18	01/02/2020 11:14	01/02/2020 11:23

I tried this but it didn't work

Interval(StartTime - EndTime, 'hh:mm')

Thanks in advance!

[QFabian]

Hi @DB6000 , you can try changing this, from 'DD-MM-YYYY' to 'DD/MM/YYYY' :

or replacing it in the field  '-' TO '/'

replace(yourdate, '-', '/') as your date.

plese let me know if it works for you.

[DB6000]

Hi @QFabian 

Thank you for the prompt reply, appreciate your help!  I changed the date format and I still don't see the correct results.  I am creating a chart (Bar chart) to show  elapsed time in hh:mm from start date/time to enddate/time. basically the runtime of the job and how long it took.  I don't have a separate column for job runtime (i may need to do this calculation), hoping i could script to figure out elapsed time/date between jobs.

jobid	StartTime	EndTime	Locations
4	01/02/2020 09:56	01/02/2020 10:08	9
9	01/02/2020 10:48	01/02/2020 10:56	8
206	01/03/2020 11:00	01/03/2020 11:15	81
212	01/03/2020 11:07	01/03/2020 11:54	2
386	01/06/2020 16:50	01/06/2020 16:54	2
387	01/06/2020 16:51	01/06/2020 16:58	22
942	01/09/2020 21:26	01/09/2020 22:13	486
943	01/09/2020 21:26	01/09/2020 21:34	10

a chart something like this attached file. showing number of jobs runtime per month/locations

 

[QFabian]

HI @DB6000 , here i worte an option for you :

Here the script, notice my variables, i didnt touch them :

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='$#.##0;$-#.##0';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='es-CL';
SET CreateSearchIndexOnReload=1;
SET MonthNames='ene.;feb.;mar.;abr.;may.;jun.;jul.;ago.;sep.;oct.;nov.;dic.';
SET LongMonthNames='enero;febrero;marzo;abril;mayo;junio;julio;agosto;septiembre;octubre;noviembre;diciembre';
SET DayNames='lun.;mar.;mié.;jue.;vie.;sáb.;dom.';
SET LongDayNames='lunes;martes;miércoles;jueves;viernes;sábado;domingo';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

Data_Aux:
Load * INLINE [
jobid, StartTime, EndTime, Locations
206,01/03/2020 11:00, 01/03/2020 11:15, 81
212,01/03/2020 11:07, 01/03/2020 11:54, 2
386,01/06/2020 16:50, 01/06/2020 16:54, 2
387,01/06/2020 16:51, 01/06/2020 16:58, 22
942,01/09/2020 21:26, 01/09/2020 22:13, 486
943,01/09/2020 21:26, 01/09/2020 21:34, 10
4,01/02/2020 09:56, 01/02/2020 10:08, 9
9,01/02/2020 10:48, 01/02/2020 10:56, 8
];

Data:
Load
*,
time((EndTime - StartTime)) as ElapsedTime;
Load
jobid as JobId,
date#(StartTime, 'DD/MM/YYYY hh:mm') as StartTime,
date#(EndTime, 'DD/MM/YYYY hh:mm') as EndTime,
Locations
Resident Data_Aux;
drop table Data_Aux;

Hope works for you!