reliability rating

He has: 688 posts

Joined: Feb 2001

After just learning about the "Substr" function from replies to my previous thread, I figured I could also create something like a data reliability rating. All of my rows have a timestamp(Cool automatically assigned to it to record the date of when the data was last updated. I figure I could make something that works like:

'if the record is 1 month old, reliability = 95%'
'if the record is 6 months old, reliability = 80%'
'if the record is 1 year old, reliability = 50%'
'if the record is 2 years old, reliability = 30%'

My question is that I was wondering how to get today's date in the 8 char format (20021104), or today's month and today's year separately? Then I can do some subtraction to make the if/then statements. ((Or is there a magic mySQL function that can automatically tell me how long it's been since a timestamp was made?))

Thanks

P.S. Thanks Mark and "necrotic" for help with my previous questions. Everything I asked about worked out well.

Mark Hensler's picture

He has: 4,048 posts

Joined: Aug 2000

SELECT
    if(PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(date_field, '%Y%m'))>24, '30%',
        if(PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(date_field, '%Y%m'))>=12,'50%',
            if(PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(date_field, '%Y%m'))>=6,'80%',
                if(PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(date_field, '%Y%m'))=1,'85%','100%')
            )
        )
    ) as reliability
FROM table_name
'mySQL Docs: 6.3.4 Date and Time Functions

Quote:
PERIOD_DIFF(P1,P2)

Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values:

mysql> SELECT PERIOD_DIFF(9802,199703);
-> 11

Mark Hensler
If there is no answer on Google, then there is no question.

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.