MySQL datetime insert

February 5, 2009 2:20 PM

Inserting a date into MySQL? Is there a to_date() equivalent to convert a string to a datetime?

I can do this in Oracle:

insert into table_name
(date_field)
values
(to_date('05/03/2003 21:02:44', 'mm/dd/yyyy hh24:mi:ss'));

Is there a similar to_date() function in MySQL that will let me format a string? Everything I'm finding on the web says that the only way to do it is if your string is already in 'YYYY-MM-DD HH:MM:SS' format (that's from the manual)

I have a few hundred records in a text file that I need load into a table, so I can't use tricks like now() and I really don't want to have to fix a all those dates. Ideas? Suggestions?

posted by exhilaration to Computers & Internet (4 answers total)

I'm pretty sure it has to be in MySQL's preferred format. I would write a quickie Python script to convert the dates to the right format and insert them into the database. Something like:

for line in open('thefile.txt','r'):
    #(split the line as needed to isolate the date)
    od = strptime(orig_date,orig_format)
    nd = od.strftime(new_format)
    #Put MySQL insert stuff here

If you don't know Python, then pretty much every scripting language has strftime and strptime. Here's the documentation for the formatting, so for example if you needed to convert from MM/DD/YY to DD-MM-YY, orig_format would be "%m/%d/%y" and new_format would be "%d-%m-%y". Again, that formatting is the same in other languages.
posted by DecemberBoy at 2:34 PM on February 5, 2009

SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
-> '2004-04-31'

Since this is MySQL, check for warnings and select min and max values for a sanity check -- depending on your settings, it will cheerfully insert bullshit given bad or ambiguous input. Note also that the function's return value can be of type date, time, or datetime, dependingon inut.
posted by orthogonality at 2:34 PM on February 5, 2009

orthogonality's suggestion is good provided you can lose the time part of the "datetime". You also might consider using a UNIX_TIMESTAMP field for ease of loading and converting from there. Here is the list of MySQL 5.1 date and time functions.
posted by McGuillicuddy at 3:12 PM on February 5, 2009

Best answer: Oh yeah, STR_TO_DATE can retain time.

> select str_to_date('05/03/2003 21:02:44', '%c/%d/%Y %T:%i:%f') ;
posted by McGuillicuddy at 3:44 PM on February 5, 2009