Solved

# Subtracting two date time

• 180 views

Userlevel 1
• Member
• 4 replies

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

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