How to work out the age of a user based on date of birth?

[Amohlmann]

I want to group users by their age which range from roughly 5 years to 90. The dateofbirth field is formatted like this 1963-12-30.
Originally I thought I would just compare the epoch time of the DoB with now(), then turn that back into how many years old the user is. Now obviously this will not work for people who were born before 1970.

| search query...
| eval dateofnow=now()
| fieldformat dateofnow=strftime(dateofnow,"%Y-%m-%d")  
| eval dateofbirthTest=dateofbirth
| convert timeformat="%Y-%m-%d" mktime(dateofbirthTest)
| eval age=(now()-dateofbirthTest)
| eval ageReadable=age/60/60/24/365.25

The above query works fine for all users that were born after 1970. The next thing I thought would be to compare the current year to the year the user was born, but this would be slightly too inaccurate for what I am hoping to achieve. I feel like I am missing something super obvious. Is there an easy way to work out the age of something from before 1970 with accuracy?

[somesoni2 · Accepted solution]

Give this a try (Run anywhere sample), give age in years and additional days

| gentimes start=-1 | eval birthdate="1951-03-30" | table birthdate | rex field=birthdate "(?<birthyear>\d+)-(?<birthmonth>\d+)-(?<birthday>\d+)" | eval bdaythisyear=strptime(strftime(now(),"%Y")."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d") | eval lastbday=if(bdaythisyear>now(),strptime((tonumber(strftime(now(),"%Y"))-1)."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d"),strptime(strftime(now(),"%Y")."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d")) | eval ageyear=tonumber(strftime(lastbday,"%Y"))-birthyear | eval ageday=round((now()-lastbday)/86400)

[Amohlmann]

Wow, yes this is exactly what I needed.
You way is much more clever than my way. thanks!

[lcblucas]

Hi all,

How can I do to put my real users birthdays on eval, instead 1951-03-30?

My birthdate field is data_nascimento.

Thanks and regards!

[Amohlmann]

First out, chop off the first bit so you are left with this:

Your base query 
|rex field=data_nascimento "(?<birthyear>\d+)-(?<birthmonth>\d+)-(?<birthday>\d+)" 
| eval bdaythisyear=strptime(strftime(now(),"%Y")."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d") 
| eval lastbday=if(bdaythisyear>now(),strptime((tonumber(strftime(now(),"%Y"))-1)."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d"),strptime(strftime(now(),"%Y")."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d")) 
| eval ageyear=tonumber(strftime(lastbday,"%Y"))-birthyear 
| eval ageday=round((now()-lastbday)/86400)

Also you will need to change the regular expression in that part if your birthday format is not YYYY-MM-DD

[Amohlmann]

The only way I found to do this was to do a bit of brute force. This way is not 100% accurate but was close enough for what I wanted to do.
The first thing I did was split out the year, month, and day of the date of birth and of now(). Yes the regular expression can be tidied:

| eval yearOfNow = strftime(now(),"%Y")
| eval monthOfNow = strftime(now(), "%m")
| eval dayOfNow = strftime(now(), "%d")
| rex field=dateofbirth "(?<yearOfBirth>\d{4})"
| rex field=dateofbirth "-(?<monthOfBirth>\d{2})-"
| rex field=dateofbirth "\d{4}-\d{2}-(?<dayOfBirth>\d{2})"

Then assuming none of the dates are before year 0 you can very very roughly work out the number of days between date of birth and now by doing something like this:

 |eval daysToBirth = (yearOfBirth*365.25) + ((monthOfBirth-1)*30) + (dayOfBirth)
 |eval daysToToday = (yearOfNow*365.25) + ((monthOfNow-1)*30) + (dayOfNow)
 |eval yearsOld = (daysToToday-daysToBirth)/365.25

So if you use the example of someone being born on 06/05/1965 and you try to work out their age (from today which is 12/01/2016) you get the following

yearOfBirth=1965
monthOfBirth=5
dayOfBirth=6

yearOfNow=2016
monthOfNow=1
dayOfNow=12

daysToBirth=717842.25
daysToNow=736356

Which gives you the number of days between the dates as ROUGHLY 18513.75
Which makes

yearsOld=50.687

This example gives you a reasonably accurate answer (the correct answer is actually 18513 days exactly). The inaccuracy is due to the fact I am cheating and saying that each month in a year is 30 days long.

[lcblucas]

Hi Amohlmann,

I´m new splunk user and not familiar with splunk commands yet. Did you have success to your request? I don´t undertand the expression that you are using.

I need the same of you, extract the user age from birth date. Can you help me, please?

My field is birthdate and have this formatting: %d%m%y.

After yhis, my ideia is create 3 age groups. Example:

1 - Up 18 years old
2 - 18 - 25 years old
3 - 25+ years old

Does anybody can help me?

Thanks and regards!

[Amohlmann]

I never got a super accurate way of doing it. The closest I got was to split out and get the year month and day in separate fields for both the date of birth and now().
Then assuming none of the dates are before year 0 you can very very roughly work out the number of days between date of birth and now by doing something like this:

|eval daysToBirth = (yearOfBirth*365.25) + ((monthOfBirth-1)*30) + (dayOfBirth)
|eval daysToToday = (yearOfNow*365.25) + ((monthOfNow-1)*30 + (dayOfNow)
|eval yearsOld = (daysToToday-daysToBirth)/365.25

So if you use the example of someone being born on 06/05/1965 and you try to work out their age (from today which is 12/01/2016) you get the following

yearOfBirth=1965
monthOfBirth=5
dayOfBirth=6

yearOfNow=2016
monthOfNow=1
dayOfNow=12

daysToBirth=717842.25
daysToNow=736356

Which gives you the number of days between the dates as ROUGHLY 18513.75
Which makes

yearsOld=50.687

This example gives you a reasonably accurate answer (the correct answer is actually 18513 days exactly). The inaccuracy is due to the fact I am cheating and saying that each month in a year is 30 days long.

The reason I have not accepted this as the answer as it is not accurate. It is close enough for what I am doing but I was hoping someone out there as a much cleverer way of doing it instead of my brute force attempt.

[javiergn]

Rather than converting to epoch try using some of the existing algorithms to get the number of days from a date and the reverse operation. Then substract days_now and days_dateOfBirth, and apply the reverse operation to the result in order to obtain the year/month/day from there.

A quick Google search gave me this (not tested though):

https://alcor.concordia.ca/~gpkatch/gdate-algorithm.html

[ncrofts_splunk]

Hi Amohlmann,

Could this existing Splunk Answers help?
https://answers.splunk.com/answers/231771/how-to-extract-age-from-a-birthday-field-before-th.html

Cheers Nick

[aljohnson_splun]

I think this has been solved by alacer

eval age = tonumber(strftime(now(), "%Y")) - tonumber(mvindex(split(dateofbirth,"-"),0))

[Amohlmann]

This makes the age slightly older than they actually are. If someone is 16 years and 1 day old it will say they are 17.
The way I have it is a little more accurate but not nearly as elegant.