[RESOLVED] Convert VARCHAR MySQL field into MySQL dates

edziffel

Have uploaded a csv source file. One of the data fields that needs to function properly is the date column. The date data in the source file is in the format "dd/mm/yyyy hh:mm or sometimes dd/mm/yyy hh:mm:ss". Don't get me started on what the people who created the data were thinking... lol

To get the data into the database in tack, or rather exactly how it appears in the source file, it was imported as a VARCHAR, with the thinking that from there it could be queried and converted into a data format that could be used to sort by time period reference. Have full control of database, but am not a seasoned pro.

Need an opinion on what the recommended procedure to convert the MySQL text/VARCHAR data into functional date data. Is doing it from a data base even the right approach or does it need to be fixed prior to uploading?

leatherback

You could use the mysql function convert() for this, I suppose. But if they used two formats mixed, I would personally fix that before uploang; Naturally, I would then also upload the whole thing into a date colum, rather than uploading text..

edziffel

thanks for the reply leatherback

I have several files to upload. Found one that the data date column is all one format in the csv and in phpmyadmin browse view it it looks like:

  8/10/2008 0:00

When I tried to upload it to a date column, it came back all zeros. Probably has a lot to do with being clueless on how to set up the data field in the database prior to upload. What am I dong wrong?

Also could you briefly describe how you would go about cleaning up the data from the csv before insert? I'm guessing to put the column data, the dates, into an array and then...?

Also not clear on how to get the corrected data back into the csv in order to then upload.

bradgrafelman

edziffel;10985894 wrote:
When I tried to upload it to a date column, it came back all zeros. Probably has a lot to do with being clueless on how to set up the data field in the database prior to upload. What am I dong wrong?

MySQL DATETIME columns are in the format 'YYYY-MM-DD HH:MM:SS', so unless the data is in that exact format, you will need to convert it rather than just throwing it at the DATETIME field and expecting MySQL to understand what type of data you've given it.

One way to convert the date inside the SQL query itself would be to use MySQL's STR_TO_DATE() function.

edziffel;10985894 wrote:
Also could you briefly describe how you would go about cleaning up the data from the csv before insert?

I wouldn't (at least, not as far as the date values are concerned - MySQL is perfectly capable of handling the conversion itself).

edziffel;10985894 wrote:
Also not clear on how to get the corrected data back into the csv in order to then upload.

Upload to where?

Also, since we're talking about a CSV file here, do you need to do any sort of extra processing on the data, or are you simply importing it into a MySQL database? If the latter is the case, then note that you don't need to use PHP to process the CSV file at all - just use a LOAD DATA INFILE query instead.

edziffel

MySQL DATETIME columns are in the format 'YYYY-MM-DD HH:MM:SS', so unless the data is in that exact format, you will need to convert it rather than just throwing it at the DATETIME field and expecting MySQL to understand what type of data you've given it.

One way to convert the date inside the SQL query itself would be to use MySQL's STR_TO_DATE() function.

Will get on it.
**** have gone through it an it looks like that will do fine. Also in the case where different data formats were used, it looks like all you need to do is run a query in the one format to write the result to another new column. It returns null if the specified format to change is not found on a given record. Then run another query using the other format which would convert all others into a second new column. Finally run a query to replace the null values in one of the two new columns from the not null values of the other. Does that seem a good approach?

I wouldn't (at least, not as far as the date values are concerned - MySQL is perfectly capable of handling the conversion itself).

That was my thinking. What's the point of a database if you can't manipulate the data? But in general is that a good policy: Get to the data into a table as given and then work with it?

The csv is how the source data is available. The question was in reference to the situation where the people who compiled the data used two formats for dates in the same column. What should be done about that? Besides finding out where they live and T.P. ing their house. lol

*** orginaly did this several hours ago got busy and forgot to hit the submit button*******