Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11

Thread: age

  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts

    age

    does anyone have a clean short sql that would return string like
    Code:
    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)
    Code:
    LPAD(TRUNCATE(date_field,DATEDIFF(NOW(),date_field)/365),2,'0'),

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by BubikolRamios View Post
    does anyone have a clean short sql that would return string like
    Code:
    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)
    Code:
    LPAD(TRUNCATE(date_field,DATEDIFF(NOW(),date_field)/365),2,'0'),
    try this:
    Code:
    round((to_days(now()) - to_days(date_field))/365)
    best regards

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    if anyone finds any bug in this(or has second thoughts about 30), looks ok to me as far as I tested

    Code:
    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')
      )

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.

  • #5
    New Coder
    Join Date
    Feb 2009
    Posts
    32
    Thanks
    0
    Thanked 4 Times in 4 Posts
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.

  • #7
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Old Pedant View Post
    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

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.

  • #9
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Old Pedant View Post
    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.
    Code:
    years = year(now()) - year(date_field);
    once I know how many years are, I will compute month
    Code:
    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

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.

  • #11
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Old Pedant View Post
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •