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 Thu, 10 Nov 2016 16:50:43 GMT
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