topic Re: Calculating difference between times in App Development

Calculating difference between times

Wed, 13 May 2015 14:48:03 GMT

I have a series of data (to the order of 100,000+ rows of data which denotes the date and time an action was performed. The LOAD statement pulls in four different pieces of data and the structure looks like this:

Incident	Unit	DateTime	ActionTaken
1234	1	5/12/2015 18:34:15	D
1234	1	5/12/2015 18:35:00	E
1234	1	5/12/2015 18:38:27	A
1234	1	5/12/2015 19:07:32	C

I need to calculate the amount of time elapsed between actions D and E, E and A, D and A and D and C so that I can look at a specific incident and then report only what the time differences are.

I really don't have the first clue where to start.

Additionally, some of the times may not exist (for example, a unit may not perform action E) and in these cases the TimeStamp is set to 0.

Re: Calculating difference between times

MK_QSL — Wed, 13 May 2015 15:09:58 GMT

Temp:
Load
  Incident,
  Unit,
  TimeStamp(Timestamp#(DateTime,'D/M/YYYY hh:mm:ss')) as DateTime,
  If(ActionTaken = 'D', 1,
  If(ActionTaken = 'E', 2,
  If(ActionTaken = 'A', 3,
  If(ActionTaken = 'C', 4)))) as ActionFlag
Inline
[
  Incident, Unit, DateTime, ActionTaken
  1234, 1, 5/12/2015 18:34:15, D
  1234, 1, 5/12/2015 18:35:00, E
  1234, 1, 5/12/2015 18:38:27, A
  1234, 1, 5/12/2015 19:07:32, C


  3456, 1, 5/12/2015 18:34:15, D
  3456, 1, 5/12/2015 18:38:27, A
  3456, 1, 5/12/2015 19:07:32, C


  6789, 1, 5/12/2015 18:34:15, D
  6789, 1, 5/12/2015 18:35:00, E
  6789, 1, 5/12/2015 19:07:32, C
];


Final:
Load
  *,
  If(ActionFlag = 1, 0) as ActionStart,
  If(ActionFlag = 2 and Previous(ActionFlag) = 1, Interval(DateTime - Previous(DateTime),'hh:mm:ss'),0) as [E-D],
  If(ActionFlag = 3 and Previous(ActionFlag) = 2, Interval(DateTime - Previous(DateTime),'hh:mm:ss'),0) as [A-E],
  If(ActionFlag = 3 and Previous(Previous(ActionFlag)) = 1, Interval(DateTime - Previous(Previous(DateTime)),'hh:mm:ss'),0) as [A-D]
Resident Temp
Order By Incident, ActionFlag;


Drop Table Temp;

Same way you can create D-C and others...

Hint : Use

Previous(Previous(Previous(ActionFlag)))

Re: Calculating difference between times

Fri, 15 May 2015 21:31:02 GMT

I attempted to adapt your code to my system but I can't seem to get it to work. It loads the data but isn't calculating the times. The times are always 0. When I use your example, it works as expected. My script follows:

Temp:

Load

"incident_num" as Incident,

"unit_id" as Unit,

TimeStamp(Timestamp#("unit_status_datetime",'D/M/YYYY hh:mm:ss')) as DateTime,

If("cad_command_code" = 'D', 1,

If("cad_command_code" = 'E', 2,

If("cad_command_code" = 'A', 3,

If("cad_command_code" = 'C', 4)))) as ActionFlag;

SQL SELECT "incident_num",

"unit_id",

"unit_status_datetime",

"cad_command_code"

FROM nfirs.dbo."unit_status_hist"

WHERE "incident_num" like '1500001%';

Final:

Load

If(ActionFlag = 1, 0) as ActionStart,

If(ActionFlag = 2 and Previous(ActionFlag) = 1, Interval(DateTime - Previous(DateTime),'hh:mm:ss'),0) as [E-D],

If(ActionFlag = 3 and Previous(ActionFlag) = 2, Interval(DateTime - Previous(DateTime),'hh:mm:ss'),0) as [A-E],

If(ActionFlag = 3 and Previous(Previous(ActionFlag)) = 1, Interval(DateTime - Previous(Previous(DateTime)),'hh:mm:ss'),0) as [A-D]

Resident Temp

Order By Incident, ActionFlag;

Drop Table Temp;

Re: Calculating difference between times

maxgro — Fri, 15 May 2015 21:56:58 GMT

you can join the rows in a single row with all datetime

Load

Incident,

Unit,

TimeStamp(Timestamp#(DateTime,'D/M/YYYY hh:mm:ss')) as DateTime,

ActionTaken

Inline

[

Incident, Unit, DateTime, ActionTaken

1234, 1, 5/12/2015 18:34:15, D

1234, 1, 5/12/2015 18:35:00, E

1234, 1, 5/12/2015 18:38:27, A

1234, 1, 5/12/2015 19:07:32, C

3456, 1, 5/12/2015 18:34:15, D

3456, 1, 5/12/2015 18:38:27, A

3456, 1, 5/12/2015 19:07:32, C

6789, 1, 5/12/2015 18:34:15, D

6789, 1, 5/12/2015 18:35:00, E

6789, 1, 5/12/2015 19:07:32, C

];

load Incident, Unit, DateTime as DateTimeD Resident z where ActionTaken = 'D';

join (y) load Incident, Unit, DateTime as DateTimeE Resident z where ActionTaken = 'E';

join (y) load Incident, Unit, DateTime as DateTimeA Resident z where ActionTaken = 'A';

join (y) load Incident, Unit, DateTime as DateTimeC Resident z where ActionTaken = 'C';

DROP Table z;

Re: Calculating difference between times

ToniKautto — Mon, 18 May 2015 00:20:28 GMT

Attached is a simple example of how to find the smallest timestamp over Incident and Unit, and the use this to compare with the timestamp on each row. To show the difference you can use the Interval() function.

Re: Calculating difference between times

Mon, 18 May 2015 20:51:53 GMT

So here's what I've got so far...

Temp:

LOAD "incident_num" as Incident,

"unit_id" as Unit,

If("cad_command_code" = 'D', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as DispatchTime,

If("cad_command_code" = 'E', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as EnrouteTime,

If("cad_command_code" = 'A', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as ArriveTime,

If("cad_command_code" = 'C', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as ClearTime,

"situation_found_code" as "Situation Found Code",

"situation_found_code"&' - '&"SFText" as "Situation Found",

"district_code" as "District";

SQL SELECT "fire_incident_core"."situation_found_code",

"fire_incident_core"."district_code",

"fire_incident_core"."shift_code",

"fire_incident_core"."incident_num",

"unit_status_hist"."unit_id",

"unit_status_hist"."unit_status_datetime",

"unit_status_hist"."cad_command_code",

"codes_table"."description" as "SFText"

FROM nfirs.dbo."fire_incident_core"

LEFT OUTER JOIN nfirs.dbo."unit_status_hist" ON "fire_incident_core"."incident_num" = "unit_status_hist"."incident_num"

LEFT OUTER JOIN nfirs.dbo."codes_table" ON "fire_incident_core"."situation_found_code"="codes_table"."code"

WHERE "codes_table"."code_group" = '0000';

Final:

Load

Interval(EnrouteTime - DispatchTime,'ss') as Turnout,

Interval(ArriveTime-EnrouteTime) as Travel,

Interval(ArriveTime-DispatchTime) as Response

Resident Temp

Order By Incident;

Drop Table Temp;

I can use an expression to calculate the time between "enroute" and "dispatch"; however, if I try to pull it up in the load script, I don't get any data.

Any ideas?