Page 1 of 1

Clean Database - delete records older than x days

Posted: Thu Jul 10, 2014 9:17 pm
by Pik
Hello,

I implemented a OWL device to check power.
Working well; I use database saving values and in my web interface, I use highcharts to plot the history (highcharts.com).
Now, I have too many records in my database and I'd like to clean it for records older than x days.

The idea is to launch an event (midnight ?) to clean database every day.

Until now, I didn't found the method... do you have any idea ?

Kind regards.

Re: Clean Database - delete records older than x days

Posted: Fri Jul 18, 2014 10:11 pm
by emvee
Must be doable by logging in to the database:

Code: Select all

$ mysql -u domouser -p --database=domotiga
mysql> show tables;
mysql> select * from device_values_log;
Are in the output from this last command the values you want to delete?
If so, you can use a command like:

Code: Select all

delete from Table where dateadd(month, 2, lastchanged< getdate()

Re: Clean Database - delete records older than x days

Posted: Sat Jul 19, 2014 10:54 pm
by Pik
Hello,

OK, I'll try this method.. I'll give you feedback as soon as I check...

Thanks & best regards