5 Replies Latest reply: May 18, 2015 4:51 PM by Aaron Kelly

# Calculating difference between times

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:

IncidentUnitDateTimeActionTaken
123415/12/2015 18:34:15D
123415/12/2015 18:35:00E
123415/12/2015 18:38:27A
123415/12/2015 19:07:32C

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
```Temp:
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:
*,
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

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:

"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" = 'C', 4)))) as ActionFlag;

SQL SELECT "incident_num",

"unit_id",

"unit_status_datetime",

FROM nfirs.dbo."unit_status_hist"

WHERE "incident_num" like '1500001%';

Final:

*,

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

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

z:

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

];

y:

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

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

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

```Temp:
"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",
"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:
*,
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?