Athena DateDiff

Question

I am trying to do what I think is a simple date diff function but for some reason, my unit value is being read as a column ("dd") so I keep getting a column cannot be resolved error

I am using AWS Athena

My code is this

SELECT "reservations"."id" "Booking_ID"
    , "reservations"."bookingid" "Booking_Code"
    , "reservations"."property"."id" "Property_id"
    , CAST("from_iso8601_timestamp"("reservations"."created") AS date) "Created"
    , CAST("from_iso8601_timestamp"("reservations"."arrival") AS date) "Arrival"
    , CAST("from_iso8601_timestamp"("reservations"."departure") AS date) "Departure"
    , CAST("from_iso8601_timestamp"("reservations"."modified") AS date) "Modified"
    , date_diff("dd", CAST("from_iso8601_timestamp"("reservations"."created") AS date), CAST("from_iso8601_timestamp"("reservations"."arrival") AS date)) "LoS"
FROM
    "database".reservations
LIMIT 5;

I am trying to get the difference in days from the "created date" and "Arrival Date"

I have tried date_diff with DD,"DD","dd",dd,Day,day,"day" and i get the same error.

score 32 · Accepted Answer · answered Oct 10 '19 at 16:36

32

Athena is based on Presto. See Presto documentation for date_diff() -- the unit is regular varchar, so it needs to go in single quotes:

date_diff('day', ts_from, ts_to)

answered Oct 10 '19 at 16:36

Piotr Findeisen

19,480
2
52
82

2

Thanks, much appreciated! – JoeD Oct 28 '19 at 14:56
1

Also useful to note that you cannot sub anything like `'s'` for 'second` or `'d'` for day -- I was doing similar and that tripped me up since I'm so used to being able to sub in a single character in other dialects. – thleo May 04 '22 at 17:17
Thanks, @thleo. I had to use `'millisecond'` instead of `'ms'`. – Andriy Makukha Jun 05 '23 at 02:46

Athena DateDiff

1 Answers1

Linked