extract the date from a timestamp value variable in Impala

Question

How can I extract the date from a timestamp value variable in Impala?

eg time = 2018-04-11 16:05:19 should be 2018-04-11

Collide

Afaik Impala supports trunc(ts_col, 'D') – Arabella

Thanks a lot, this creates: 2018-03-26 00:00:00 - but how can I get 2018-03-26 only? – Collide

Cast to a string plus substring? – Arabella

does anybody know how perhaps do it with extract ? I saw one can do it for year, but how is it done for date? – Collide

EXTRACT only supports YEAR/MONTH/DAY as individual values, but no combination. And Impala doesn't support the DATE datatype, then it would be a simple CAST(tscol AS DATE) – Arabella

Answer 1

try this:

from_timestamp('2018-04-11 16:05:19','yyyy-MM-dd') as date_value

from_timestamp(datetime timestamp, pattern string): Converts a TIMESTAMP value into a string representing the same value. Please see documentation here

Dicarlo

While this code may resolve the OP's issue, it is best to include an explanation as to how your code addresses the OP's issue. In this way, future visitors can learn from your post, and apply it to their own code. SO is not a coding service, but a resource for knowledge. Also, high quality, complete answers are more likely to be upvoted. These features, along with the requirement that all posts are self-contained, are some of the strengths of SO as a platform, that differentiates it from forums. You can edit to add additional info &/or to supplement your explanations with source documentation. – Whisper

Answer 2

to_date (t1.local_time), as date_value

to_date: Returns a string representation of the date field from a timestamp value.

Collide