Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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
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!