topic Re: Duration Between Two Dates/Times in App Development

Duration Between Two Dates/Times

carlcimino — Fri, 04 Jun 2021 14:37:00 GMT

Hello, I am trying to calculate the time (in minutes) between a flight departure and a flight arrival. I know I have to merge the dates and time fields. However I'm unsure how to convert the time field to an actual time because of the A or P appended onto the end to designate AM or PM. Any help would be appreciated.

Here is some sample data:

FlightDateTimeDuration:
load * inline [
FLIGHT DEP DATE,FLIGHT DEP TIME,FLIGHT ARRIVAL DATE,FLIGHT ARRIVAL TIME
05/24/2021,0100P,05/24/2021,0235P
04/22/2020,0105P,04/22/2020,0420P
05/26/2021,0115P,05/26/2021,0325P
06/09/2021,0115P,06/09/2021,0232P
03/20/2020,0120P,03/20/2020,0255P
10/03/2021,0130P,10/03/2021,0324P
05/20/2021,0137P,05/20/2021,0350P
06/17/2021,0157P,06/17/2021,0305P
05/19/2021,0200P,05/19/2021,0340P
05/28/2021,0200P,05/28/2021,0325P
06/02/2021,0200P,06/02/2021,0305P
05/27/2021,0213P,05/27/2021,0355P
06/11/2021,0213P,06/11/2021,0329P
05/28/2021,0220P,05/28/2021,0430P
05/26/2021,0230P,05/26/2021,0405P
05/19/2021,0233P,05/19/2021,0438P
05/28/2021,0235P,05/28/2021,0435P
05/27/2021,0240P,05/27/2021,0725P
06/14/2021,0243P,06/14/2021,0552P
06/05/2021,0245P,06/05/2021,0431P
05/26/2021,0249P,05/26/2021,0535P
06/18/2021,0255P,06/18/2021,0439P
05/24/2021,0300P,05/24/2021,0445P
]
;

Re: Duration Between Two Dates/Times

carlcimino — Fri, 04 Jun 2021 15:24:25 GMT

Finding pieces of this all over the place but still not there yet. The A or P on the end of the departure and arrival time is still giving fits...

https://community.qlik.com/t5/QlikView-App-Dev/Time-format-including-AM-PM/m-p/297757#M110467

https://community.qlik.com/t5/App-Development/Caluculate-the-time-diff-bn-two-time-stamps/td-p/1518286

https://community.qlik.com/t5/QlikView-App-Dev/Combine-Date-and-Time-Fields-as-a-single-field-quot-TimeStamp/m-p/552899#M206593

Re: Duration Between Two Dates/Times

carlcimino — Fri, 04 Jun 2021 15:49:56 GMT

Solved it. Hope this helps! A little bit of additional date/time formatting required based on the source system data fields. The stumbling block was the format on the time fields to take the A or P into consideration --> time(Time#([TFATME],'hhmmt'),'hh:mm tt')

, interval(Timestamp(Date(Date#(TFADTE, 'YYYYMMDD'), 'MM/DD/YYYY') + time(Time#([TFATME],'hhmmt'),'hh:mm tt'), 'MM-DD-YYYY hh:mm tt') - Timestamp(Date(Date#(TFDDTE, 'YYYYMMDD'), 'MM/DD/YYYY') + time(Time#([TFDTME],'hhmmt'),'hh:mm tt'), 'MM-DD-YYYY hh:mm tt'),'hh:mm') as "Flight Duration"