If you have an intervalday, then you should be able to use *extract(day from ....)* function. Also if you have a date then you can try something like below
0: jdbc:drill:zk=10.10.100.190:5181> select datediff(NOW(), l_shipdate) from cp.`tpch/lineitem.parquet` limit 1; *+---------+* *| **EXPR$0 ** |* *+---------+* *| *7552 * |* *+---------+* 1 row selected (0.475 seconds) If you have a timestamp then cast it to a date and try the above query. - Rahul On Thu, Nov 10, 2016 at 9:01 AM, Robin Moffatt < [email protected]> wrote: > Hi, > > I get an error: > > 0: jdbc:drill:zk=local> select extract(day from cast(p.post.published_at as > interval day)) > . . . . . . . . . . . > from dfs.data.ghost_posts_rm > . . . . . . . . . . . > p; > Error: SYSTEM ERROR: IllegalArgumentException: Invalid format: > "2003-06-28T23:00:00.000Z" > > thanks > > > On 10 November 2016 at 16:50, rahul challapalli < > [email protected]> > wrote: > > > Can you try the below query? > > > > select extract(day from cast(p.post.published_at as interval day)) > > from dfs.data.ghost_posts_rm > > p; > > > > - Rahul > > > > On Thu, Nov 10, 2016 at 3:01 AM, Robin Moffatt < > > [email protected]> wrote: > > > > > Hi, > > > I have a date in a table, that I want to calculate how many days it is > > > between then and current date. > > > I have read the docs on date time formats, including intervals ( > > > http://drill.apache.org/docs/date-time-and-timestamp/), as well as > date > > > time functions ( > > > http://drill.apache.org/docs/date-time-functions-and-arithmetic/). > > > > > > I have a query that returns the interval: > > > > > > 0: jdbc:drill:zk=local> select p.post.published_at,age(p. > > > post.published_at) > > > FROM dfs.data.ghost_posts_rm p limit 5; > > > +---------------------------+-----------+ > > > | EXPR$0 | EXPR$1 | > > > +---------------------------+-----------+ > > > | 2003-06-28T23:00:00.000Z | P162M24D | > > > > > > but I can't see how to transform the INTERVALDAY into an int of days > > alone. > > > > > > Any suggestions? > > > > > > thanks. > > > > > >
