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?

Are you sure that `CAST("sessions_staging__end_raw" AS TIMESTAMP)` is not the source of the problem? Can you please share sample data? — Guru Stron, Aug 10 '22 at 12:43

score 1 · Answer 1 · answered Apr 22 '23 at 20:18

1

date(substr(timestamp_column, 1, 10)) AS date_column

answered Apr 22 '23 at 20:18

user131448

11
2

1

While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Yunnosch Apr 23 '23 at 12:53

score 0 · Accepted Answer · answered Aug 10 '22 at 13:44

0

I ended up with:

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

answered Aug 10 '22 at 13:44

Igor Shmukler

1,742
3
15
48

converting Athena timestamp to date

2 Answers2