Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Hadoop/Impala In-DB tool syntax for today's date

Highlighted
8 - Asteroid

I'm using an In-DB tool to connect to an Impala/Hadoop database.  On my Connect In-DB tool what is the syntax for today's date?  I can't get now() and unix_timestamp to work.

Thanks

Highlighted
Alteryx Partner

Does 'current_timestamp()' work? What sort of error are you getting when using now() or unix_timestamp()?

Highlighted
8 - Asteroid

Current_timestamp() doesn't work either.  Actually it doesn't give an error, it just doesn't output any data to a Browse In-DB tool.  The only way I can get it to work is by keying in the date manually in format YYYY-MM-DD

Highlighted
8 - Asteroid

Someone at my company helped me figure this out. 

The solution was:

 = to_date(from_unixtime(unix_timestamp()))

Highlighted
Alteryx Partner

Wow, good to know. Any ideas why you need to do that to get it to show?

Highlighted
8 - Asteroid

I think it was because the field in Hadoop was formatted as a date (without any time details).  So the now() and unix_timestamp trials were too specific and running on the exact second the workflow was run.  So it was good SQL but didn't spit out any results, but the to_date piece made it possible.

Highlighted
Alteryx Partner

Makes a lot of sense, thanks for sharing!

Labels