...

View Full Version : age



BubikolRamios
02-20-2009, 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
02-20-2009, 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
02-20-2009, 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
02-22-2009, 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
02-22-2009, 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
02-23-2009, 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
02-23-2009, 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
02-23-2009, 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
02-23-2009, 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
02-23-2009, 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
02-23-2009, 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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum