From a CSV file into Mysql - How to transfer dates?

[ruffy]

I remember from a Sitepoint book about the
advantage of formatting dates as 10-digit integers
to render PHP, Mysql and javascript go easier.

I acted accordingly.

Now, however, I need help:
How do I get dates from a CSV file
into their corresponding Mysql integer-formatted fields;
What form must these CSV data take?

Thank you!

[longneck]

there are three ways to accomplish this:

first is to load it in to excel or whatever and format the column as yyyy-mm-dd h:mm:ss, then save.

second is to create a staging table in the database where this column is varchar. load the CSV file in to that table, then use INSERT ... SELECT to move the rows to the correct table, and use date formatting or string manipulation functions to get the dates in there correctly.

third option is just like the second, but instead of a staging table, use LOAD DATE INFILE to reformat the dates. here's an example from the mysql manual:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

[ruffy]

Thank you longneck.
Not quite sure what you said.

Here's how I used to do the inserts:

I'd get the right values from a csv (Excel) file.
I do that by opening that CSV file with a text editor (TextWrangler),
take the headings (or titles of columns) and
incorporate them (with a "replace", or simple edits) into the
INSERT clause,
then take the rest of the file data, and
(again with replace and edits) trasform
the data to fit into as many VALUES clauses as there are rows.
That's how I construct my entire sql INSERT statement.

It's perhaps longer than the LOAD DATA way
but the latter never seemed to work for me.

But now that I need to fill a Mysql (10-digit) integer field
with a date, that shows up in the CSV file as a string that looks like
this: "2008-12-23" I ran into problems because the wrong
value shows up in the table.

I hope you follow me.

[longneck]

ah, i completely missed the part about 10 digit integers, a.k.a., unix timestamps.

i must disagree with the recommendation that unix timestamps are easier to deal with. considering the ease with which mysql DATE, TIME and DATETIME values can be converted to/from unix timestamps using the from_unixtime() and unix_timestamp() formats, and the added benefit of being able to easily process the mysql native DATE, TIME and DATETIME types, storing unix timestamps natively yields very little benefit.