drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rahul challapalli <challapallira...@gmail.com>
Subject Re: INTERVAL date arithmetic
Date Mon, 14 Nov 2016 18:03:56 GMT
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 <
robin.moffatt@rittmanmead.com> 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 <
> challapallirahul@gmail.com>
> 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 <
> > robin.moffatt@rittmanmead.com> 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.
> > >
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message