Subtracting two date time

Hi everyone I got an issue, 

How can I subtract the arrival and depart (date_time) in to deviation (hours) and extract it to difference. 

ffb3ec50-8f7d-474d-9b72-a89923be3899.png

I have done the deviation by using this:

case(
  when(diff_days(${arrival}, ${depart}) = 0
    , (extract_hours(${depart}) - extract_hours(${arrival})) +  

(extract_minutes(${depart}) - extract_minutes(${arrival}))/60 +

(extract_seconds(${depart}) - extract_seconds(${arrival}))/3600)
   , diff_minutes(${arrival},${depart})/60)

and the difference I did the same but it got an issue like this: 

7c66909f-71cd-4ca3-a4a0-61abdded628b.png

Many thanks

Solved Solved
2 1 931
1 ACCEPTED SOLUTION

Deviation (hour): 

case(
  when(diff_days(${date 1}, ${date 2}) = 0
    , abs((extract_hours(${time 1}) - extract_hours(${time 2})) + 
    (extract_minutes(${time 1}) - extract_minutes(${time 2}))/60 + 
    (extract_seconds(${time 1}) - extract_seconds(${time 2}))/3600))
    , abs(diff_minutes(${time 1},${time 2})/60)
)

Calculate table calculation for each time/minute/hour/second to achieve the difference

Ex: Second

if(${seconds_arrival}=0, concat("",""), if(${seconds_arrival}=1, concat(${seconds_arrival}, " second "), concat(${seconds_arrival}, " seconds ")))

Second arrival:

case(
  when(diff_days(${date 1}, ${date 2}) = 0,
    floor(mod(abs(((extract_hours(${time 1}) - extract_hours(${time 2}))*3600) + 
    ((extract_minutes(${time 1}) - extract_minutes(${time 2}))*60)+ 
    (extract_seconds(${time 1}) - extract_seconds(${time 2}))),60))),
    floor(abs(mod(diff_seconds(${time 1},${time 2}),60)))
)

View solution in original post

1 REPLY 1

Deviation (hour): 

case(
  when(diff_days(${date 1}, ${date 2}) = 0
    , abs((extract_hours(${time 1}) - extract_hours(${time 2})) + 
    (extract_minutes(${time 1}) - extract_minutes(${time 2}))/60 + 
    (extract_seconds(${time 1}) - extract_seconds(${time 2}))/3600))
    , abs(diff_minutes(${time 1},${time 2})/60)
)

Calculate table calculation for each time/minute/hour/second to achieve the difference

Ex: Second

if(${seconds_arrival}=0, concat("",""), if(${seconds_arrival}=1, concat(${seconds_arrival}, " second "), concat(${seconds_arrival}, " seconds ")))

Second arrival:

case(
  when(diff_days(${date 1}, ${date 2}) = 0,
    floor(mod(abs(((extract_hours(${time 1}) - extract_hours(${time 2}))*3600) + 
    ((extract_minutes(${time 1}) - extract_minutes(${time 2}))*60)+ 
    (extract_seconds(${time 1}) - extract_seconds(${time 2}))),60))),
    floor(abs(mod(diff_seconds(${time 1},${time 2}),60)))
)

Top Labels in this Space
Top Solution Authors