0

TL;DR: I have values like 2018-09-10T08:55:09.705Z in a CSV I want to import into a SQL table, what should be the type of the field in the table?

I'm trying to migrate data from mongoDB collection into MySQL, I export data into a CSV file and then I try to import them using MySQL workbench table import wizard, but for the date field createdAt with values like 2018-09-10T08:55:09.705Z it gives me this error:

- Row import failed with error: ("Incorrect datetime value: '2018-09-10T08:55:09.705Z' for column 'createdAt'

I get this same error trying SQL DATE and DATETIME types.

I'm not sure if I need to convert the date to something like Date values used in SQL or convert Date values to timestamps or there is a way to do this with the current data format.

ganjim
  • 1,234
  • 1
  • 16
  • 30
  • 1
    Also [`STR_TO_DATE()`](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date) and the related [`DATE_FORMAT()`](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format) for full reference on the "format string" options. Though they are the [standard ones used almost everywhere.](http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html) – Neil Lunn May 13 '19 at 10:18
  • @NeilLunn Thanks I will read them, but can you tell me why SQL can't read UTC date exported from mongo? I googled and it looks like the only difference between SQL and mongoDB date is the `Z` character in the end – ganjim May 13 '19 at 10:21
  • 1
    It just expects a different format than [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601). Always has, so I don't see that changing any time soon. – Neil Lunn May 13 '19 at 10:23
  • @NeilLunn So I need to see the mongo UTC date as an string and convert it somehow using STR_TO_DATE() function? – ganjim May 13 '19 at 10:25

0 Answers0