You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Robin Moffatt <ro...@rittmanmead.com> on 2016/11/10 11:01:30 UTC
INTERVAL date arithmetic
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.
Re: INTERVAL date arithmetic
Posted by rahul challapalli <ch...@gmail.com>.
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.
> > >
> >
>
Re: INTERVAL date arithmetic
Posted by Robin Moffatt <ro...@rittmanmead.com>.
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 <ch...@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.
> >
>
Re: INTERVAL date arithmetic
Posted by rahul challapalli <ch...@gmail.com>.
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.
>