4

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

Jino Michel Aque
  • 513
  • 1
  • 4
  • 16
  • Maybe give a link to the version of Athena which you are using. – Tim Biegeleisen Oct 22 '18 at 13:31
  • The error message is saying that `from_unixtime` expects a double, but the provided argument `created` is of type `varchar` (the SQL name for a Hive String type). So you need something like list `from_unixtime(cast(created as DOUBLE))` – Dain Sundstrom Oct 22 '18 at 19:05

1 Answers1

4

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.

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82