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
Asked
Active
Viewed 2.5k times
7
-
Afaik Impala supports `trunc(ts_col, 'D')` – dnoeth Jun 24 '18 at 20:35
-
Thanks a lot, this creates: 2018-03-26 00:00:00 - but how can I get 2018-03-26 only? – Anna Jun 24 '18 at 20:54
-
Cast to a string plus substring? – dnoeth Jun 24 '18 at 20:56
-
does anybody know how perhaps do it with extract ? I saw one can do it for year, but how is it done for date? – Anna Jun 24 '18 at 21:18
-
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)` – dnoeth Jun 24 '18 at 21:20
2 Answers
11
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
hidsan
- 121
- 1
- 6
-
5While 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. – ysf Jun 24 '20 at 08:24
3
to_date (t1.local_time), as date_value
to_date: Returns a string representation of the date field from a timestamp value.
Anna
- 444
- 1
- 5
- 23