Re: Rolling retention

[Peter Brawley]

> The insert statement came from a MySQL reference manual web page.

If you say so, but from there to here it acquired fatal errors. I guess you didn't try it before posting it. The multiple Insert rows aren't comma-separated. Each datetime value is invalid twice over--it's not quoted, and it's in the weird US m/d/Y format rather than in the international format that MySQL uses. The Insert values need to look like this ...

(460725, str_to_date( '1/1/2018 12:37:23 AM', '%m/%d/%Y %r' ), ...

The MySQL function str_to_date() does what its name says; see its manual page. Three successive mass replaces on each set of Inserts in a text editor will get you from what you conjured to the above for each row ...

(i) mass replace commas , with , str_to_date('

(ii) mass replace M) with M', '%m/%d/%Y %r') ),

(iii) replace the final comma in each result of [ii] with a semicolon

Table creation syntax has to be ...

CREATE TABLE user(userId INT unsigned, created datetime );
CREATE TABLE activity_log(userId INT unsigned, created datetime);

.. but no primary keys are defined. (All datetime values are current so you might consider timestamps rather than datetimes.)

Pasting the result of all that into the mysql client program gets you two working MySQL tables.

The query looks like SQL Server syntax. Date parsing and value casting have to be changed to the MySQL dialect. Before we get to that, though, what version of MySQL are you running, and if it's not 8, why not?