substr at Amazon Athena

amazon-athena presto
Akihiro Seki picture Akihiro Seki · Jul 27, 2017 · Viewed 26k times · Source

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

jens walter picture jens walter · Jul 27, 2017

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.

Related questions

Presto check if NULL and return default (NVL analog)

Is there any analog of NVL in Presto DB? I need to check if a field is NULL and return a default value. I solve this somehow like this: SELECT CASE WHEN my_field is null THEN 0 ELSE my_field …

Read More
Athena greater than condition in date column

I have the following query that I am trying to run on Athena. SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date However it is producing this …

Read More
Athena date format unable to convert string to date formate

tried the below syntax none of them helped to convert a string type column to date select INVC_,APIDT,APDDT from APAPP100 limit 10 select current_date, APIDT,APDDT from APAPP100 limit 10 select date_format( b.APIDT, '%Y-%m-%d') …

Read More