How to find quarter stemp diff by sql please?

Question

I use mysql, and these time columns are not all in the same year.

I use this:

SELECT TIMESTAMPDIFF(QUARTER, DATE_FORMAT('2018-03-30', '%Y-%m-%d'), DATE_FORMAT('2018-09-30', '%Y-%m-%d'))
FROM DUAL;

It returns 2, but this:

SELECT TIMESTAMPDIFF(QUARTER, DATE_FORMAT('2018-03-31', '%Y-%m-%d'), DATE_FORMAT('2018-09-30', '%Y-%m-%d'))
FROM DUAL;

It returns 1.

Now I want the second returns 2 too, because March in quarter 1 and September in quarter 3, I just want it return 3-1=2.

I use this now:

SELECT (YEAR('2018-09-30') - YEAR('2018-03-31')) * 4 + (QUARTER('2018-09-30') - QUARTER('2018-03-31'))
FROM DUAL;

Is it the right way to do this? and how to do this right please?

Answer 1

You can achieve the correct answer just using the QUARTER() function:

SELECT QUARTER('2018-09-30') - QUARTER('2018-03-31') AS QuarterDifference
FROM DUAL;

QuarterDifference
-----------------
2

Here's a demo of this: SQL Fiddle