[NMLUG] mysql query

[havoc at harrisdev.com (Jody Harris)]

I've posted a more readable version here:
http://www.galacticslacker.com/perl-bin/re?folder=MySQL

jody

Jody Harris wrote:
> It's a pretty dangerous thing, but I admit that I think when I drive. 
> After leaving the house today, I realized a work-around for this problem.
> 
> date_sub() x 2
> 
> Here's a query that will give you your hit-or-miss days since event 
> without having to do external tests:
> 
> SELECT DATE_FORMAT(
>     DATE_SUB(   
>         FROM_DAYS(
>             (
>                 TO_DAYS( CURDATE( ) ) - TO_DAYS( DATE_SUB( "2004-03-30", 
> INTERVAL 1 YEAR) )
>             )
>         ) , INTERVAL 1 YEAR)
>     , "%y years %m months %e days")
>  AS alldays;
> 
> 
> I've tried to break it done to make it easier to digest.
> 
> jody
> 
> Andres Paglayan wrote:
> 
>>   Hi Jody,
>> sorry to bother you,
>> I am having a problem with the query when the days values are less 
>> than 365, it returns all zeores, -> 0000-00-00, instead of 0000-10-03
>> I am fixing that outside mysql using php,
>> mysql doc says that shouldn't be used before 14century, but couple of 
>> days inaccuracy are fine,
>> do you also get 0000-00-00 for less than 365 days difference, or is a 
>> version's bug?
>> anyway, many thanks, the query helps a lot,
>>
>> Jody Harris wrote:
>>
>>> I'm blushing!
>>>
>>> Thanks for the too-kind words.  You do realize that the numbers this 
>>> query reports are going to be in error +/- 1 day, depending on leap 
>>> days and stuff, right?
>>>
>>> jody
>>>
>>> Andres Paglayan wrote:
>>>
>>>> Kudos to this query, is perfect,
>>>> deserves to be posted in the mysql documentation,
>>>> Thank you,
>>>>
>>>>
>>>> Jody Harris wrote:
>>>>
>>>>> Uh, this is a cheat, but.....
>>>>>
>>>>> select date_format(from_days( TO_DAYS( CURDATE( ) ) - TO_DAYS( 
>>>>> "1969-03-30" ) ), "%y years %m months %e days") AS alldays;
>>>>>
>>>>> yields:
>>>>> 35 years 07 months 2 days
>>>>>
>>>>> The cheat is that this is based on the false assumption that the 
>>>>> years, months days is based on the UNIX epoch (1970-01-01).
>>>>>
>>>>> jody
>>>>>
>>>>> Andres Paglayan wrote:
>>>>>
>>>>>> Hi All,
>>>>>> does anybody knows a good query that calculates age including 
>>>>>> months and days using mysql date functions?
>>>>>> I tried SELECT CURDATE( ) - dob AS age but it does bogus things 
>>>>>> when the month in dob is bigger than the current,
>>>>>> TO_DAYS( CURDATE( ) ) - TO_DAYS( dob ) AS alldays will give me the 
>>>>>> total days, but I am seeing no way to transform those in '1 year 3 
>>>>>> months 5 days'  from within mysql ,
>>>>>> I google it and there is plenty of calculations for the year, but 
>>>>>> nothing for months and days inclusive,
>>>>>> and it has to work before epoc,
>>>>>> Thank you,
>>>>>> Andres
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>> -- 
>> Andres Paglayan
>> andres@paglayan.com <mailto:andres@paglayan.com>
>> Ph: (505) 986-1561
>> Santa Fe, NM USA
>>
>> Open Source is like Love. The more you share it the better it gets.
>>
> 

-- 
http://www.RealizationSystems.com/ -- start communicating
http://www.GalacticSlacker.com/ -- read it and weep
http://www.NMPerspective.com/ -- a Southwest Perspective