I want to make a program in SQL or in crontab to export the table to CSV, but everywhere I find examples with only a static file name. And I need to add the date and time when it was created to the file name.

    3 answers 3

    mysqldump database tablename > /path/to/dump.`date '+%Y%m%d-%H:%M'`.sql

    If you want CSV, you will need to either write your own single-line pearl-barley script ™, or use the date in the directory name:

     CDT=`date '+%Y%m%d-%H:%M'` mkdir /path/to/base/dir/$CDT/ && mysqldump -T/path/to/base/dir/$CDT/ dbname table_name --fields-terminated-by=','

    In addition, a good version of the CSV dumper itself is described here , and you already know how to wrap its output in a file with the correct name from the first line of my answer.

    • Actually, he doesn’t need a dump ... - Akina
    • @Akina Yeah, I get it, but late. But I understood, so I wrote the last paragraph. - Alexander Prokoshev
     SET @sql := CONCAT('SELECT * INTO OUTFILE \'\\\\folder_chain\\\\filenamename_prefix_', DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d'), '.csv\' FROM table_name;'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
    • I get an error, something is wrong here. - Roman
    • No need to retell in your own words. Quote. - Akina

    I generally managed to make a request, it remains to figure out how to make an automatic procedure.

     SET @time := NOW() + 0; SET @sql := CONCAT ('SELECT `ReceivedAt`, `Message` ', 'INTO OUTFILE ', '\'c:/TEMP/export', @time, '.csv', '\' ', 'FIELDS TERMINATED BY \';\' ENCLOSED BY \'"\' FROM \`aqua_meter\` WHERE 1;'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;