Date math with Django SQL Explorer-CodePudding

I have implemented Django SQL Explorer on a project and am attempting to build a query that will pull entries between today's date and 12 months prior to today's date, but I'm having a difficult time figuring out how SQL Explorer does date math. So far, about the only thing I've been able to discover is that SQL Explorer uses current_date for today's date.

So:

SELECT current_date;

returns:

2021-10-02

I've tried using the MySQL implementation (since that's the database my project is using):

SELECT current_date - INTERVAL 12 MONTH;

returns:

near "12": syntax error

About the closest I've been able to come is some very simple math that just work on the year and strips all the rest of the date information away:

SELECT current_date - 1;

returns:

Can anyone please help me figure out how to return the date 12 months prior to today's date in Django SQL Explorer?

SELECT current_date
SELECT current_date - [12 MONTH];

should return:

2021-10-02
2020-10-02

Thanks in advance!

CodePudding user response：

You must use DATE_SUB function in MySQL syntax. for example:

SELECT DATE_SUB('2021-10-02', INTERVAL 1 YEAR);

SELECT DATE_SUB('2021-10-02', INTERVAL 12 MONTH);

CodePudding user response：

I suspect that you are using SQLite and not MySql, in which case you want:

SELECT date(CURRENT_DATE, '-12 month')

Or:

SELECT date(CURRENT_DATE, '-1 year')