View Full Version : age
BubikolRamios 02202009, 01:06 PM does anyone have a clean short sql that would return string like
12/08/01
menaing 12 years,8 months ,1 day
I mean doing this way would produce huge sql for simple task
(this is just year part)
LPAD(TRUNCATE(date_field,DATEDIFF(NOW(),date_field)/365),2,'0'),
oesxyl 02202009, 02:24 PM does anyone have a clean short sql that would return string like
12/08/01
menaing 12 years,8 months ,1 day
I mean doing this way would produce huge sql for simple task
(this is just year part)
LPAD(TRUNCATE(date_field,DATEDIFF(NOW(),date_field)/365),2,'0'),
try this:
round((to_days(now())  to_days(date_field))/365)
best regards
BubikolRamios 02202009, 09:11 PM if anyone finds any bug in this(or has second thoughts about 30), looks ok to me as far as I tested
concat
(
LPAD(FLOOR(DATEDIFF(NOW(),date_field)/365.25),2,'0'),'/',
LPAD(FLOOR(MOD(DATEDIFF(NOW(),date_field),365.25)/30),2,'0'),'/',
LPAD(CAST(MOD(MOD(DATEDIFF(NOW(),date_field),365.25),30)as signed),2,'0')
)
Old Pedant 02222009, 03:44 AM I think it depends on how accurate you need/want it to be.
Clearly that code *CAN* give some answers that will be off by as much as 3 days (if I just did the math in my head right).
If you want it *really* correct, I'd write a stored proc.
`Jeremy 02222009, 10:19 PM It's really more advisable to just store a timestamp with now() or time(). They're vastly more accurate and you can format them however you wish when you need to retrieve them from the database.
Old Pedant 02232009, 05:52 AM Jeremy: I think you miss the point of the question.
He has a date *ALREADY STORED* in the database. Think of it as, say, a DOB (Date Of Birth) of a person.
He wants to know the AGE of that person (or post or object or whatever) *ON THE CURRENT DATE*.
His general formula is okay: Get the number of days, divide by 365.25 to get (roughly) the number of years. Take the remainder and divide by 30 to get (roughly) the number of months. Take the remainder and that's the number of days old in the month.
But you can see that pretty much by definition it's going to be inaccurate by 5 days on December 31st for a person born on Jan 1st. That would be day number 364 (or 365 in leap year) and so it would end up being month *THIRTEEN* and day 4 of month 13. Not the best of schemes, to say the least.
p.s.: So I goofed; the max error is obviously AT LEAST 4 days. I suspect there's a way to get a 5 day inaccuracy, but I haven't tried real hard to track it down.
oesxyl 02232009, 07:11 AM His general formula is okay: Get the number of days, divide by 365.25 to get (roughly) the number of years. Take the remainder and divide by 30 to get (roughly) the number of months. Take the remainder and that's the number of days old in the month.
maybe is just a typo but leap years have 366 days and normal years 365, :)
will not affect the division since the medium days in years remain 365.25
best regards
Old Pedant 02232009, 07:45 AM Example of why 365.25 does not always work right:
Start date: 1/1/2008
Today's date: 1/1/2009
Number of days between: 366 (because 2008 was leap year).
Divide by 365.25 to get 1 year.
Then subtract (see his code):
==> 366  FLOOR( 1 * 365.25 ) ==>> 366  365 ==>> you would end up answering 1 year, 0 months, 1 day when the correct answer is 1 year, 0 months, 0 days.
Yes, 365.25 is an *AVERAGE*, but it breaks down when you want *EXACT* answers in "human" terms.
oesxyl 02232009, 07:58 AM Example of why 365.25 does not always work right:
Start date: 1/1/2008
Today's date: 1/1/2009
Number of days between: 366 (because 2008 was leap year).
Divide by 365.25 to get 1 year.
Then subtract (see his code):
==> 366  FLOOR( 1 * 365.25 ) ==>> 366  365 ==>> you would end up answering 1 year, 0 months, 1 day when the correct answer is 1 year, 0 months, 0 days.
Yes, 365.25 is an *AVERAGE*, but it breaks down when you want *EXACT* answers in "human" terms.
I would do by difference using mysql buildin functions.
years = year(now())  year(date_field);
once I know how many years are, I will compute month
months = period_diff(date_format(now(),'%Y%m'),date_format(date_add(date_field, interval years year),'%Y%m)
and a little complicate probable for days. I guess there is a more simple way to do this but in principle I will avoid to compute intervals using aritmetic operators.
best regards
Old Pedant 02232009, 08:46 AM Yep, that's the basic idea.
But I don't think I'd attempt it in an "ad hoc" query. Would make so much more sense to do it in a Stored Proc, where you could use variables and manipulate things easier.
As I said way back when, it depends on the degree of accuracy the original poster needs.
oesxyl 02232009, 09:05 AM Yep, that's the basic idea.
But I don't think I'd attempt it in an "ad hoc" query. Would make so much more sense to do it in a Stored Proc, where you could use variables and manipulate things easier.
As I said way back when, it depends on the degree of accuracy the original poster needs.
yes, I am and was agree from the begining, :)
best regards

