substr at Amazon Athena

Question

At Amazon Athena, I want to extract only the character string "2017-07-27" from the character string "2017-07-27 12:10:08".

SELECT SUBSTRING (event_datetime.s, 0, 10) FROM production limit 10

I tried it like this which only returns numbers 0 to 10.

At Athena, is it possible to cut character strings? If so, how can I do it?

Or, if you know how to cast "2017-07-27 12:10:08" to date type, that's fine.

Thank you.

Answer 1

You can use SUBSTR to substring a column value.

Here is the string function reference page.

In your case, this would lead to the following statement:

SELECT SUBSTR(event_datetime.s, 1, 10) FROM production limit 10

NOTE that the index position of the first character is 1 (not zero), the same as in standard SQL.