Search code examples
sqlapache-drillsql-date-functions

How to extract the date part from a timestamp field in apache drill?

I have a timestamp field in a drill table (say date_time), How can I extract the date field only from the same. Saw lots of date operation functions here but none of them helped.

Here is the sample data,

+------------------------+
|       date_time        |
+------------------------+
| 2017-01-01 03:45:58.0  |
| 2017-01-01 21:42:20.0  |
| 2017-01-01 15:08:47.0  |
| 2017-01-01 19:59:39.0  |
| 2017-01-01 22:37:24.0  |
+------------------------+

I need an output like follows,

+-------------+
|  date_time  |
+-------------+
| 2017-01-01  |
| 2017-01-01  |
| 2017-01-01  |
| 2017-01-01  |
| 2017-01-01  |
+-------------+

I don't want to do string operations. :)

Solution

  • Please try this, if dates are properly formatted in your table. A simple method

    select cast(date_time as date) date_time from table