reliability rating
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( 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 posted this at 21:42 — 4th November 2002.
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
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.