Time Difference or Time in Human Readable Format

Kushal_Chawda · Sep 21, 2022 1:04:33 PM

Hi Qlikers,

Let's say I have time difference between two dates (possibly StartDate & EndDate) as "42:19:36 (hh:mm:ss)", but now user want to read it like "1 Day, 18 Hours, 19 Minutes, 36 Seconds",which is nothing but we can call it as human readable format.

First step to get the time difference in human readable format is to convert the time difference in Seconds, so that it will be easy to convert the seconds to human readable format. But data format may vary as per the business rules, for eg. you may have TimeStart and TimeEnd Columns in data source using which we need to find the time difference or you may have time difference pre-calculated as a column in a Seconds or in a hours or in a minutes. Sometimes time difference is stored in hh:mm:ss format in data source.

Let's Demonstrate the each case

1) Case when you have TimeStart and TimeEnd Columns in data source.

a) When you simply want the time difference in hh:mm:ss format, you can use the Interval function. Make sure that your date columns are in proper Timestamp format, if not then use Timestamp# function to make it proper Timestamp format.

Interval (TimeEnd - TimeStart, 'hh:mm:ss')

In case if the dates are in text format then use the below.

Interval (Timestamp#(TimeEnd,'DD/MM/YYYY hh:mm:ss') - Timestamp#(TimeEnd,'DD/MM/YYYY hh:mm:ss'), 'hh:mm:ss')

b) When you want the time difference in human readable format.

We are going to convert the time difference in seconds so that it will be easy to convert the seconds to human readable format.

floor((TimeEnd-TimeStart)*24*60*60)

Note: Difference between two timestamp will always be in Days although you apply Interval function on top of that to convert in hh:mm:ss format. Interval function just give you the format but difference still will be in days.

Below is the complete script to get the Human readable format of time difference.

Map:
mapping LOAD * Inline [
MapFrom,MapTo
"0%," ,];

Timestamp:
LOAD *,
ltrim(MapSubString('Map',Time_Diff1)) as Time_Diff;
LOAD *,
Years&if(Years>1, ' Years', if(Years=1,' Year','%')) &', '&
Months&if(Months>1, ' Months', if(Months=1,' Month','%')) &', '&
Days&if(Days>1, ' Days', if(Days=1,' Day','%')) &', '&
Hours&if(Hours>1, ' Hours', if(Hours=1,' Hour','%')) &', '&
Minutes&if(Minutes>1, ' Minutes', if(Minutes=1,' Minute','%')) &', '&
Seconds&if(Seconds>1, ' Seconds', if(Seconds=1,' Second','%')) as Time_Diff1;
LOAD *,
                            floor(TimeInSeconds/31104000) as Years,
floor(mod(TimeInSeconds,31104000) / 2592000) as Months,
        floor(mod(TimeInSeconds,2592000) /86400) as Days,
              floor(mod(TimeInSeconds,86400) /3600) as Hours,
                    floor(mod(TimeInSeconds,3600) /60) as Minutes,
                                          mod(TimeInSeconds,60) as Seconds;
LOAD *,
Interval(TimeEnd-TimeStart,'hh:mm:ss') as Time_hhmmss,
floor((TimeEnd-TimeStart)*24*60*60) as TimeInSeconds;
LOAD * Inline [
TimeStart, TimeEnd
21/03/2016 12:11:34, 22/03/2016 02:34:12
20/03/2016 10:14:34, 22/03/2016 04:34:10
18/03/2016 10:15:34, 22/03/2016 04:40:10
20/03/2016 10:14:30, 20/03/2016 10:34:10
21/03/2016 09:11:20, 21/03/2016 09:11:50
21/01/2016 08:11:20, 24/03/2016 09:20:50
20/04/2014 10:09:20, 21/03/2016 11:20:50 ];

2) Case when you have a time difference pre-calculated as a Second or as a Hour or as a Minute.

a) When you simply want to display the time difference as hh:mm:ss format.

interval(Seconds/86400,'hh:mm:ss')

interval(Minutes/1440, 'hh:mm:ss')

interval(Hours/24, 'hh:mm:ss')

b) When you want to display the time difference in human readable format.

If you have a time difference as a seconds then no need to do any conversion, you can follow the steps in Case 1.

If you have a time difference as a Hours then do Hours*60*60 to convert it in seconds and follow the steps in Case 1

If you have a time difference as a Minutes then do Minutes*60 to convert it in seconds and follow the steps in Case 1

3) Case when you have a time difference as hh:mm:ss format.

Below is the script to convert the time difference from hh:mm:ss to Seconds and follow the steps in Case 1 to convert it in human readable format

Time:
LOAD *,

Interval#(Time,'hh:mm:ss') as TIME ,

rangesum(SubField(Time,':',1)*60*60 ,subField(Time,':',2)*60 ,subField(Time,':',3)) asTimeInSeconds Inline[
Time
2923:55:57
389:45:43
889:05:36