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

    Calculating difference between times

    Aaron Kelly

      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
          Manish Kachhia
          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
              Aaron Kelly

              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
              Massimo Grossi

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

               

              z:

              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 

              ]; 

               

              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
                Toni Kautto

                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
                  Aaron Kelly

                  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?