I created a table that has a timestamp DATETIME column and I am trying to import data from CSV files that have Unix timestamped rows.
However, when I try to use DataGrip's CSV import feature, I get errors to the tune of
1:1: conversion failed: "1394669640" to datetime
2:1: conversion failed: "1394670060" to datetime
3:1: conversion failed: "1394670480" to datetime
4:1: conversion failed: "1394670780" to datetime
Maybe dataGrip is not the best tool, but that's pretty much all I can do with, given that I don't have direct access to the machine which runs the DB server[*]. Obviously this works if I set the column to be INT.
Is there a way of configuring the table so that the conversion happens automatically?
[*] I do, but the DBMS is running inside a Docker container so, short of copying the CSV files on the host and then inside the container, I can't play tricks like LOAD DATA INFILE and set the column with FROM_UNIXTIME().
You can use LOAD DATA LOCAL INFILE to load a CSV file to a remote MySQL (i.e. inside a docker container), without needing to copy the CSV file into the container.
mysql> create table MyTable (timestamp DATETIME PRIMARY KEY);
mysql> load data local infile 'data.csv' into table MyTable (@dummy)
SET timestamp = FROM_UNIXTIME(@dummy);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from MyTable;
+---------------------+
| timestamp |
+---------------------+
| 2014-03-13 00:14:00 |
| 2014-03-13 00:21:00 |
| 2014-03-13 00:28:00 |
| 2014-03-13 00:33:00 |
+---------------------+
Note the local-infile option needs to be enabled both on your server, to allow remote clients, and also in the client. To do the example above, I had to enable local-infile=1 in my server's /etc/my.cnf and I also ran the client with the option mysql --local-infile. Without both of those options, I got this very confusing error:
ERROR 1148 (42000): The used command is not allowed with this MySQL version
Read https://dev.mysql.com/doc/refman/5.7/en/load-data-local.html for details.