Time Difference or Time in Human Readable Format

[Kushal_Chawda]

How can we represent the time difference between two dates (such as StartDate and EndDate) in a 'human-readable' format, like '1 Day, 18 Hours, 19 Minutes, 36 Seconds'?

The first step is to convert the time difference into seconds, making it easier to translate into a human-readable format. However, the data format may vary depending on business rules. For example, you might have TimeStart and TimeEnd columns in your data source, requiring you to calculate the time difference, or you might have the time difference pre-calculated in seconds, hours, or minutes. In some cases, the time difference might be stored in hh:mm
format in the data source.

Let's demonstrate each scenario

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

We will convert the time difference into seconds, making it easier to translate the seconds into a human-readable format

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

 Note: The difference between two timestamps will always be expressed in days, even if you apply the Interval function, as the Interval function is merely a formatting tool

Below is the complete script for converting the time difference into a human-readable format.

Timestamp:
LOAD *,
    if(Years>0,Years & ' Year, ','') &
    if(Months>0,Months & ' Month, ','') &
    if(Days>0,Days & ' Day, ','') &
    if(Hours>0,Hours & ' Hour, ','') &
    if(Minutes>0,Minutes & ' Minute, ','') &
    if(Seconds>0,Seconds & ' Second') as Time_Diff;

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

If the time difference is expressed in seconds, no conversion is necessary; you can follow the steps in Case 1.

If the time difference is in hours, multiply the value by 60 * 60 to convert it to seconds, and then follow the steps in Case 1.

If the time difference is in minutes, multiply the value by 60 to convert it to seconds, and then follow the steps in Case 1.

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

Below is the script to convert the time difference from hh:mm
to seconds. You can then follow the steps in Case 1 to convert it into a human-readable format.

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

Please feel free to offer any suggestions to improve this document.

[Joaquin_Lazaro]

Good job Kush141087

Thanks for sharing

[settu_periasamy]

Good One. Thank you for Sharing..

[psankepalli]

Nice thought.. Thank you for sharing..