Delete 30 days old data

Hey, I want to delete the data from the tc_positions which is 30 days old . i am using PostgreSQL so kindly help me.

This Script is for MySQL maybe you can try and work around.

Lets say you have traccar database called "new_d"

and

you need to clean table called "tc_positions" which

takes most of the database "new_d"

and

your password for database is: "traccar111" and

user is "root"

1. We will make sh file

nano clean-db.sh

copy and paste bellow code (correct the database name, user and password according to yours in the script!)

#!/bin/bash

result=""

while [[ "$result" != " 0 rows affected"* ]]; do result=$(mysql -u root -ptraccar111 new_d -vve "DELETE FROM tc_positions WHERE fixTime <

DATE(DATE_ADD (NOW(), INTERVAL -90 DAY)) AND id

NOT IN (SELECT positionld FROM tc_devices WHERE

positionid IS NOT NULL) LIMIT 1000")

sleep 1 done

1. Make the clean-db.sh executable with this command

chmod +x clean-db.sh

1. We want that our auto database script start cleaning the database late at night 23:00 and to auto stop the cleaning proccess at 6:00 AM

in the morning

and every day the same proccess will be continued. So we need a cronjob

In the command line start the crontab by entering this

command:

crontab -e

then copy and paste this code bellow:

0 23 *** * /root/clean-db.sh >> /root/logs/clean-db.log

2>&1

then we need to stop the proccess at 6 PM, past this code bellow:

0 6*** pkill -f clean-db.sh

and thats it, we created auto delete script with cron job so we do not need manually database cleaning!

Credit @AnonymousLearning

This worked for me. Just add your mysql details and days history. Worked for mysql

cat > /etc/cron.daily/traccar-clear-database << EOF
#!/bin/bash
result=""
while [[ "$result" != " 0 rows affected" ]]; do
result=$(mysql -u root -proot traccar -vve "DELETE FROM tc_positions WHERE fixTime < DATE(DATE_ADD(NOW(), INTERVAL -7 DAY)) AND id NOT IN (SELECT positionId FROM tc_devices WHERE positionid IS NOT NULL) LIMIT 10000")
sleep 1
done
result=""
while [[ "$result" != " 0 rows affected" ]]; do
result=$(mysql -u root -proot traccar -vve "DELETE FROM tc_events WHERE eventTime < DATE(DATE_ADD(NOW(), INTERVAL -7 DAY)) LIMIT 10000")
sleep 1
done
EOF

Then run below

chmod +x /etc/cron.daily/traccar-clear-database