Mysql queries filtered by date

[rudio]

Hi all,

New to Vicidial and want to do some custom reporting.

Use to Asterisk but cant seem to get the reports filtered by date as I want to.

I would like to report from the call_log tables only from the 1 March 2012 to 7 March 2012 for instance. Per todays date and from to current date etc.

mysql -D asterisk -e "select * from call_log where date(calldate) > '2012-03-01' order by calldate;
Tried changing date(calldate) to column headers inside tables and filter like that, but its just dumping the whole tables from beginning to end.

Not sure if this belongs on General or support

Any help would be appresiated.

Rudi

[williamconley]

You have to convert the text to dates. > a text field is not the same as > a numerical field. And since the text in that field is actually a date, comparing that date to some text may not elicit the desired results.

Use the mysql web site, it has a lot of hints for date comparisons.

Or get a mysql developer to write it for you.

Or use the reports in Vicidial! Press the "Reports" link.

Oh: And the secret to OpenSource: If you find a report that contains the function you want (the ability to grab data based on date range?): copy that file, find the sql query being generated ... and voila! Quite often "DB=1" in the URL will cause some interesting screen display.