[DELCDR]
dsn=asterisk
writesql=DELETE FROM asterisk.cdr WHERE date = ${curdate} - INTERVAL 1 MONTH
My question is, Am I correct to use ‘writesql’ ? and how do I apply this in my dialplan?
exten => h,1,SET(ODBC_DELCDR()) <<<< I know this is wrong, anybody knows the correct way for the dialplan?
Assuming you need to delete records for the same date of last month, I think you need something like this.
DELETE FROM asterisk.cdr WHERE DATE(YOUR_DATETIME_FIELD_NAME_HERE) = DATE( NOW() - INTERVAL 1 MONTH)
You can go through this to understand the basics of func_odbc
Hi @satish4asterisk
Thank you for your reply. I know for a fact that the MySQL query work already tested it. My problem is the dial plan. I only have exten => h,1,SET(ODBC_DELCDR()) which does not work
Is this from your func_odbc? I don’t understand how ${curdate} will work in your query. Do you try to pass something from your dialplan to func_odbc? Do you have a field named date in your cdr table?
I think you need = symbol on the right side of this function call.
exten => h,1,SET(ODBC_DELCDR()=)
${curdate} is a variable i set in the dialplan. And yes I have a column ‘date’ on my cdr table. Already tried putting an ‘=’ sign on the dialplan. Still doesnt work.
Hi @satish4asterisk and @sedwards
I have a custom field in my CDR which is ‘date’ only. And I set this on my dialplan by
exten => _X.,n,SET(CDR(date)=${ODBC_CURDATE()})
My corresponding func_odbc for $${ODBC_CURDATE()}) is
[CURDATE]
dsn=asterisk
readsql=SELECT (CURDATE()) as curdate
it just gets the current date and inserts it on cdr table after each call. Now what I want to do is filter out 30 day old CDR entries and delete the them using
[DELCDR]
dsn=asterisk
writesql=DELETE FROM asterisk.cdr WHERE date = ${curdate} - INTERVAL 1 MONTH
You don’t need to pass ${curdate} in that case.
exten => h,1,SET(ODBC_DELCDR()=)
and
DELETE FROM asterisk.cdr WHERE date <= DATE( NOW() - INTERVAL 1 MONTH)
should work. If it doesn’t then provide Asterisk CLI output