Re: Converting INTERVAL to Number
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 < aengelbre...@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 Omernikwrote: > > > > 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 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
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 Omernikwrote: > > 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 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
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 Omernikwrote: > 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 >
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