You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by John Omernik <jo...@omernik.com> on 2016/05/25 14:35:50 UTC
Converting INTERVAL to Number
Hey all, simple question, I have a field, dob, I want to get the current
age from...
I have:
cast(age(dob) as INTERVAL YEAR) as yr_age
Which works pretty well, as you can see below, however, I'd like a column
that is just the integer age, no months, no P/Y etc. Now, I can play with
string manipulation for a really ugly query, but I was hoping there may be
a way to just convert the INTERVAL YEAR (or heck even the return of the age
function) to the number of years...
So instead of P25Y, it would be 25, P52Y1M would be 52, P21Y8M would be 21
etc.
Any easy way to do this? If I have to go the string route, is there an easy
way to do it in drill as well (I miss the Hive "regexp_extract" in this
case)
John
select dob, age(dob) as cur_age, cast(age(dob) as INTERVAL YEAR) as yr_age
from am_joined where substr(dob, 6, 2) <> '00' limit 10;
+-------------+------------------+----------+
| dob | cur_age | yr_age |
+-------------+------------------+----------+
| 1991-09-29 | P300M5DT18000S | P25Y |
| 1965-01-06 | P625M17DT18000S | P52Y1M |
| 1995-01-12 | P260M4DT18000S | P21Y8M |
| 1988-08-01 | P338M19DT18000S | P28Y2M |
| 1984-03-05 | P392M9DT18000S | P32Y8M |
| 1980-12-13 | P431M17DT18000S | P35Y11M |
| 1976-11-28 | P480M23DT18000S | P40Y |
| 1984-11-12 | P383M27DT18000S | P31Y11M |
| 1965-01-20 | P625M3DT18000S | P52Y1M |
| 1984-04-19 | P390M24DT18000S | P32Y6M
Re: Converting INTERVAL to Number
Posted by John Omernik <jo...@omernik.com>.
Yep, that works without the extra CAST, I didn't see the Y in the original
age return, I assumed it had to be as year before I could use it, I assumed
wrong. I guess that's my superpower, being wrong and answering my own
questions on lists so others can learn from my mistakes ;)
On Wed, May 25, 2016 at 10:51 AM, Andries Engelbrecht <
aengelbrecht@maprtech.com> wrote:
> Great!
>
> Answering your own questions on email lists can be therapeutic ;-)
>
> Why not just use
> EXTRACT(year from age(dob))
>
> --Andries
>
>
> > On May 25, 2016, at 7:39 AM, John Omernik <jo...@omernik.com> wrote:
> >
> > Well I need to include the Staples "That was Easy" button here... I
> tried:
> >
> > EXTRACT(year from cast(age(dob) as INTERVAL YEAR)) as yr_age
> >
> > And it worked!
> >
> > Self Answering question is self answering...
> >
> >
> >
> >
> > On Wed, May 25, 2016 at 9:35 AM, John Omernik <jo...@omernik.com> wrote:
> >
> >> Hey all, simple question, I have a field, dob, I want to get the current
> >> age from...
> >>
> >> I have:
> >>
> >> cast(age(dob) as INTERVAL YEAR) as yr_age
> >>
> >> Which works pretty well, as you can see below, however, I'd like a
> column
> >> that is just the integer age, no months, no P/Y etc. Now, I can play
> with
> >> string manipulation for a really ugly query, but I was hoping there may
> be
> >> a way to just convert the INTERVAL YEAR (or heck even the return of the
> age
> >> function) to the number of years...
> >>
> >> So instead of P25Y, it would be 25, P52Y1M would be 52, P21Y8M would be
> 21
> >> etc.
> >>
> >>
> >> Any easy way to do this? If I have to go the string route, is there an
> >> easy way to do it in drill as well (I miss the Hive "regexp_extract" in
> >> this case)
> >>
> >>
> >> John
> >>
> >>
> >> select dob, age(dob) as cur_age, cast(age(dob) as INTERVAL YEAR) as
> >> yr_age from am_joined where substr(dob, 6, 2) <> '00' limit 10;
> >>
> >>
> >> +-------------+------------------+----------+
> >>
> >> | dob | cur_age | yr_age |
> >>
> >> +-------------+------------------+----------+
> >>
> >> | 1991-09-29 | P300M5DT18000S | P25Y |
> >>
> >> | 1965-01-06 | P625M17DT18000S | P52Y1M |
> >>
> >> | 1995-01-12 | P260M4DT18000S | P21Y8M |
> >>
> >> | 1988-08-01 | P338M19DT18000S | P28Y2M |
> >>
> >> | 1984-03-05 | P392M9DT18000S | P32Y8M |
> >>
> >> | 1980-12-13 | P431M17DT18000S | P35Y11M |
> >>
> >> | 1976-11-28 | P480M23DT18000S | P40Y |
> >>
> >> | 1984-11-12 | P383M27DT18000S | P31Y11M |
> >>
> >> | 1965-01-20 | P625M3DT18000S | P52Y1M |
> >>
> >> | 1984-04-19 | P390M24DT18000S | P32Y6M
> >>
>
>
Re: Converting INTERVAL to Number
Posted by Andries Engelbrecht <ae...@maprtech.com>.
Great!
Answering your own questions on email lists can be therapeutic ;-)
Why not just use
EXTRACT(year from age(dob))
--Andries
> On May 25, 2016, at 7:39 AM, John Omernik <jo...@omernik.com> wrote:
>
> Well I need to include the Staples "That was Easy" button here... I tried:
>
> EXTRACT(year from cast(age(dob) as INTERVAL YEAR)) as yr_age
>
> And it worked!
>
> Self Answering question is self answering...
>
>
>
>
> On Wed, May 25, 2016 at 9:35 AM, John Omernik <jo...@omernik.com> wrote:
>
>> Hey all, simple question, I have a field, dob, I want to get the current
>> age from...
>>
>> I have:
>>
>> cast(age(dob) as INTERVAL YEAR) as yr_age
>>
>> Which works pretty well, as you can see below, however, I'd like a column
>> that is just the integer age, no months, no P/Y etc. Now, I can play with
>> string manipulation for a really ugly query, but I was hoping there may be
>> a way to just convert the INTERVAL YEAR (or heck even the return of the age
>> function) to the number of years...
>>
>> So instead of P25Y, it would be 25, P52Y1M would be 52, P21Y8M would be 21
>> etc.
>>
>>
>> Any easy way to do this? If I have to go the string route, is there an
>> easy way to do it in drill as well (I miss the Hive "regexp_extract" in
>> this case)
>>
>>
>> John
>>
>>
>> select dob, age(dob) as cur_age, cast(age(dob) as INTERVAL YEAR) as
>> yr_age from am_joined where substr(dob, 6, 2) <> '00' limit 10;
>>
>>
>> +-------------+------------------+----------+
>>
>> | dob | cur_age | yr_age |
>>
>> +-------------+------------------+----------+
>>
>> | 1991-09-29 | P300M5DT18000S | P25Y |
>>
>> | 1965-01-06 | P625M17DT18000S | P52Y1M |
>>
>> | 1995-01-12 | P260M4DT18000S | P21Y8M |
>>
>> | 1988-08-01 | P338M19DT18000S | P28Y2M |
>>
>> | 1984-03-05 | P392M9DT18000S | P32Y8M |
>>
>> | 1980-12-13 | P431M17DT18000S | P35Y11M |
>>
>> | 1976-11-28 | P480M23DT18000S | P40Y |
>>
>> | 1984-11-12 | P383M27DT18000S | P31Y11M |
>>
>> | 1965-01-20 | P625M3DT18000S | P52Y1M |
>>
>> | 1984-04-19 | P390M24DT18000S | P32Y6M
>>
Re: Converting INTERVAL to Number
Posted by John Omernik <jo...@omernik.com>.
Well I need to include the Staples "That was Easy" button here... I tried:
EXTRACT(year from cast(age(dob) as INTERVAL YEAR)) as yr_age
And it worked!
Self Answering question is self answering...
On Wed, May 25, 2016 at 9:35 AM, John Omernik <jo...@omernik.com> wrote:
> Hey all, simple question, I have a field, dob, I want to get the current
> age from...
>
> I have:
>
> cast(age(dob) as INTERVAL YEAR) as yr_age
>
> Which works pretty well, as you can see below, however, I'd like a column
> that is just the integer age, no months, no P/Y etc. Now, I can play with
> string manipulation for a really ugly query, but I was hoping there may be
> a way to just convert the INTERVAL YEAR (or heck even the return of the age
> function) to the number of years...
>
> So instead of P25Y, it would be 25, P52Y1M would be 52, P21Y8M would be 21
> etc.
>
>
> Any easy way to do this? If I have to go the string route, is there an
> easy way to do it in drill as well (I miss the Hive "regexp_extract" in
> this case)
>
>
> John
>
>
> select dob, age(dob) as cur_age, cast(age(dob) as INTERVAL YEAR) as
> yr_age from am_joined where substr(dob, 6, 2) <> '00' limit 10;
>
>
> +-------------+------------------+----------+
>
> | dob | cur_age | yr_age |
>
> +-------------+------------------+----------+
>
> | 1991-09-29 | P300M5DT18000S | P25Y |
>
> | 1965-01-06 | P625M17DT18000S | P52Y1M |
>
> | 1995-01-12 | P260M4DT18000S | P21Y8M |
>
> | 1988-08-01 | P338M19DT18000S | P28Y2M |
>
> | 1984-03-05 | P392M9DT18000S | P32Y8M |
>
> | 1980-12-13 | P431M17DT18000S | P35Y11M |
>
> | 1976-11-28 | P480M23DT18000S | P40Y |
>
> | 1984-11-12 | P383M27DT18000S | P31Y11M |
>
> | 1965-01-20 | P625M3DT18000S | P52Y1M |
>
> | 1984-04-19 | P390M24DT18000S | P32Y6M
>