Page 1 of 1
Years/months since x
Posted: Fri Jul 07, 2006 5:33 pm
by LiveFree
Hey All,
As part of my applacation I am building a 'time in service' clock which takes a timestamp (yyyy-mm-dd yes I know its weird) and translates it into the total amount of years+months since that date
I have been thinking about how to do this and I need a few suggestions.
I was thinking turning that into a timestamp and then dividing that by 365 and 30 respectively.
Thanks

Re: Years/months since x
Posted: Fri Jul 07, 2006 7:47 pm
by bdlang
Tucker wrote:Hey All,
... takes a timestamp (yyyy-mm-dd yes I know its weird)...
Is the date provided by a database call, by chance? If so, you can likely perform all the date calculations within an SQL statement.
I was thinking turning that into a timestamp and then dividing that by 365 and 30 respectively.
You mean a UNIX_TIMESTAMP?
Posted: Fri Jul 07, 2006 9:04 pm
by printf
Use period_diff(), the first value you take from your date or time column, the second value, you can format current date or just supply a year and month!
example.... (return total months)
Code: Select all
period_diff(DATE_FORMAT(DateColumn, '%Y%m'), DATE_FORMAT(CURDATE(),'%Y%m')) AS months
if you want to do it all in your query (year, month), then you will need to use IF(), to test and then do your math operation before returning the value for (year, month), it not that difficult, you can use the same operation as above for years, by just using %Y, removing %m, but you still need to test, so you only return the remaining months that are left from the years! If you don't mind having a decimal single you can just use years and return the years as a decimal value.
pif!