Athena DateDiff

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.

1 answer

  • answered 2019-10-10 16:36 Piotr Findeisen

    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)