View Full Version : age

BubikolRamios

02-20-2009, 12: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, 01: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, 08: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, 02: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, 09: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, 04: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, 06: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, 06: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, 06: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, 07: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, 08: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

Powered by vBulletin® Version 4.2.2 Copyright © 2015 vBulletin Solutions, Inc. All rights reserved.