I need to show date_diff by day as a decimal value in Presto Athena

Question & Answer
,
1

Hi I have two date fields that I am trying to get a decimal value for the diff by day
at present I can do it by minutes , but need it to show like 4.3 days
my current code getting minutes is ,
“date_diff”(‘minute’, scheduled__arrival_time_local, actual_arrival_time_local) “Diff”

so if I have start date 24/06/22 05:20 end date 24/07/22 18:35
The diff would be 30.552083 days , which I would like it to show to 1 decimal point so 30.5

2

Hi,

you have to divide the difference in minutes by 1440.0

60*24 = 1440

3

Thanks , yes I tried that , but I still am unable to get it to show as a decimal value , it is always a whole number

4

ok… do you use 1440 or 1440.0?

otherwise you have to CAST the datediff to decimal.

CAST(“date_diff”(‘minute’, scheduled__arrival_time_local, actual_arrival_time_local) AS DECIMAL(5,1))/1440.0

this should do the trick

1 Like
5

Thanks I managed to get that to work including ROUND
ROUND(CAST(“date_diff”(‘minute’, “Arrival Date”, “Depart Date”) AS DECIMAL(10,1))/1440.0,1) “Ground_Event_Duration”