CODEWITHSUNDEEP
mysql
user797963
user797963

Reputation: 3027

MySQL - bulk inserting data with datetime and timestamp fields

So, I'm playing around with a database trying to learn MySQL. I've got a script together that creates a database and am not trying to bulk populate the data. Everything seems fine except for my fields that are datetime and timestamp.

Here's the table I'm having the issue with:

DROP TABLE IF EXISTS TestMsgs;
CREATE TABLE TestMsgs (
    msg_id int NOT NULL AUTO_INCREMENT, 
    user_id int,
    content varchar(200), 
    time_submitted datetime,
    last_update timestamp,
    PRIMARY KEY (msg_id)
) ENGINE=INNODB;

So, I want to manually set the datetime when a record is added, and then auto update the timestamp every time a record is updated.

When I try to batch populate the following data:

LOAD DATA LOCAL INFILE "testmsgs.txt" REPLACE INTO TABLE TestMsgs
    FIELDS TERMINATED BY '|' (user_id, content, time_submitted);

content of testmsgs.txt:
    1|test1|10/22/2013 10:30
    1|test2|10/22/2013 10:31
    1|test3|10/22/2013 10:32

I get the following results:

+----------+---------+---------+---------------------+-------------+
|  msg_id  | user_id | content | time_submitted      | last_update |
+----------+---------+---------+---------------------+-------------+
|        1 |       1 | test1   | 0000-00-00 00:00:00 | NULL        |
|        2 |       1 | test2   | 0000-00-00 00:00:00 | NULL        |
|        3 |       1 | test3   | 0000-00-00 00:00:00 | NULL        |
+----------+---------+---------+---------------------+-------------+

What gives? What is the proper way to do this?

Upvotes: 0

Views: 4079

Answers (2)

Mike Brant
Mike Brant

Reputation: 71414

Please note what @ceejayoz has said about proper format for datetime and timestamp fields.

To make your timestamp field work the way you want, you need to change your last_update field definition to be as follows:

last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

The DEFAULT CURRENT_TIMESTAMP definition provide current timestamp on record creation. The ON UPDATE CURRENT_TIMESTAMP definition provides the functionality to update the timestamp on record changes.

Upvotes: 0

ceejayoz
ceejayoz

Reputation: 180065

MySQL's TIMESTAMP/DATETIME format is YYYY-MM-DD HH:MM:SS. You're doing MM/DD/YYYY HH:MM, so MySQL hasn't the first clue what to do with it. You'll need to convert with MySQL's STR_TO_DATE function.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

Upvotes: 2

Related Questions