-1

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.

Sofyan Thayf
  • 1,322
  • 2
  • 14
  • 26

2 Answers2

1

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.

Nick
  • 138,499
  • 22
  • 57
  • 95
0

These statements both yields same result. It is up to you.

select ABS(TIMESTAMPDIFF(MONTH, '2018-01-01', '2010-01-01') / 12); --Result 8

select ABS(TIMESTAMPDIFF(YEAR, '2018-01-01', '2010-01-01') )   --Result 8
Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72