converting Athena timestamp to date

Question

I am running a query against Athena, and it breaks. Specifically, I get an error for the below fragment:

  avg(
    DATE_DIFF(
      'minute',
      CAST(from_iso8601_timestamp("sessions_staging".session_start_at) AS TIMESTAMP),
      CASE
        WHEN CAST("sessions_staging__end_raw" AS TIMESTAMP) + INTERVAL '1' MINUTE > CAST("sessions_staging".next_session_start_at AS TIMESTAMP) THEN CAST("sessions_staging".next_session_start_at AS TIMESTAMP)
        ELSE CAST("sessions_staging__end_raw" AS TIMESTAMP) + INTERVAL '30' MINUTE
      END
    )
  ) "sessions_staging__average_duration_minutes"

Athena complains with Value cannot be cast to timestamp: 2022-08-03T00:05:54.300Z.

I tried a bunch of tricks like casting my date to string then casting again to a time or a timestamp type. A similar problem caused by the same issue is covered some in converting to timestamp with time zone failed on Athena

The value seems to be just fine. I am able to execute: SELECT CAST(From_iso8601_timestamp('2022-08-03T00:05:54.300Z') AS timestamp). If I do not use CAST() and just do: "sessions_staging".session_start_at, it says that (varchar(6), varchar, timestamp) for function date_diff so I know that session_start_at is perceived as VARCHAR. However, for the type of casting described as a solution to my issue to work, in the linked discussion, SELECT need to be used, it seems. Everything that I tried including string manipulations did not work.

How could I re-write my query/casts for Athena to process my request?

Answer 1

I ended up with:

CAST(DATE_PARSE(my_varchar_date, '%Y-%m-%dT%H:%i:%s.%f%z') AS TIMESTAMP)