Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Solved

Subtracting two date time

  • 24 October 2022
  • 1 reply
  • 180 views
H
Userlevel 1

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. 

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: 

 

Many thanks

icon

Best answer by honghai.sg99-1657099840 9 January 2023, 04:01

View original

1 reply

H
Userlevel 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)))
)

 

Reply


Terms of Service