mysqltimestampdiff

Calculate average number of years with TIMESTAMPDIFF

I need to calculate the average number of years from my MySQL database, and I try to use TIMESTAMPDIFF

Which one gives a valid result:

AVG( TIMESTAMPDIFF(MONTH, tanggal_masuk, tanggal_yudisium )/12 )

or

AVG( TIMESTAMPDIFF(YEAR, tanggal_masuk, tanggal_yudisium ) )

tanggal_masuk and tanggal_yudisium columns are DATE type.

Solution

  • It depends how accurate you want your final result to be. Using TIMESTAMPDIFF with MONTH and dividing by 12 will give floating point results (e.g. 0.5 years) where using it with YEAR will only give integers (e.g. TIMESTAMPDIFF(YEAR, '2018-09-01', '2018-01-10') is 0) where TIMESTAMPDIFF(MONTH, '2018-09-01', '2018-01-10') / 12 gives -0.5833. Overall, using MONTH will give more accurate results.