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?

Use the beginning of the quarter instead of the end. That will insulate your from end-of-month issues. — Gordon Linoff, Aug 14 '18 at 12:14
Thank you. I use SELECT DATE(CONCAT(YEAR(CURDATE()), '-', ELT(QUARTER(CURDATE()), 1, 4, 7, 10), '-', 1)) FROM DUAL; to get the beginning of the quarter. — xunitc, Aug 14 '18 at 13:13

score 0 · Answer 1 · answered Aug 14 '18 at 13:07

0

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

answered Aug 14 '18 at 13:07

Barry Piccinni

Thank you. but like I said, the time columns are not all in the same year. – xunitc Aug 14 '18 at 13:14

1 Answers1