Casting String type to Unix Date Amazon Athena

user_4967382 0
Viewed 8708

I'm looking to get a result in Amazon Athena were I can count the quantity of users created by day (or maybe by month)

But previous that I have to convert the unix timestamp to another date format. And this is where i fail.

My last goal is to convert this type of timestamp:

1531888605109

In something like:

2018-07-18

According to Epoch Converter

But when I try to apply the solution i saw in this quiestion: Casting unix time to date in Presto

I got the error:

[Simba]AthenaJDBC An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 1:13: Unexpected parameters (varchar) for function from_unixtime. Expected: from_unixtime(double) , from_unixtime(double, bigint, bigint) , from_unixtime(double, varchar(x)) [SQL State=HY000, DB Errorcode=100071]

This is my query:

select   cast(from_unixtime(created)as date) as date_creation, 
         count(created) 
from     datalake.test
group by date_creation

Maybe I've to cast over the sctring because the data type of the field is not a date.

My table description: Link to the table description

presto amazon-athena date
1 Answers

line 1:13: Unexpected parameters (varchar) for function from_unixtime. Expected: from_unixtime(double)

This means that your timestamps -- even though they appear numeric -- are varchars. You need to add a CAST to cast(from_unixtime(created)as date), like:

CAST(from_unixtime(CAST(created AS bigint)) AS date)

Note: When dealing with time-related data, please have in mind that https://github.com/prestosql/presto/issues/37 is not resolved yet in Presto.

user_654580
Related