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.

score 37 · Answer 1 · edited Jun 15 '18 at 18:32

37

You can use SUBSTR to substring a column value.

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.

edited Jun 15 '18 at 18:32

Alex R

answered Jul 27 '17 at 07:27

jens walter

5

strange how left() and right() don't work but this does – David Andrei Ned Dec 16 '19 at 15:28
Note that the second parameter is a length, not the end position from a char-array perspective -- see substr(string, start, length) → varchar in the reference page in above answer .... – JosephDoggie Apr 07 '21 at 15:16

1 Answers1