I came across this mysql issue and I am unsure how to solve it, I am writing a script to a remote machine, and the script would execute once a month. One of the problems is that there are too much data so I have to split the insert into 4 weeks for example.

echo "insert into audit_bak3 select * from audit where op_time >= $FIRSTDAY_date and op_time <= $NEXTRUN;"| mysql -uroot -pfirstpassword $DB

Here you can see that I am trying to insert data from audit to audit_bak3 form a FIRSTDAY_date= 2022-07-01 to NEXTRUN= 2022-08-01

The problem that I think I have related to the fact that the date format in the table is including time: 2022-07-01 20:12:45

But I am not sure, I don't know how to handle this situation and would love some help or direction!

Thank you for all the help!

CodePudding user response：

You need quotes around dates. Otherwise 2022-07-01 is treated as a numeric subtraction expression.

echo "insert into audit_bak3 select * from audit where op_time BETWEEN '$FIRSTDAY_date' AND '$NEXTRUN';"