Set timestamp on insert when loading CSV

Question

I have a Timestamp field that is defined to be automatically updated with the CURRENT_TIMESTAMP value.
It works fine when I fire a query, but when I import a csv (which I'm forced to do since one of the fields is longtext) , the update does not work.

I have tried to:

Give timestamp column as now() function in csv
Manually enter timestamp like 2013-08-08 in the csv

Both the approaches do not work

We can't help unless you show your code? How are you inserting the csv? are you using phpMyAdmin, some third party code or did you write a one-off import script? what does the CSV look like? show the `CREATE TABLE` query, too, for good mesure. Does the field that should contain the timestamp have a default value? — Elias Van Ootegem, Aug 07 '13 at 10:57
Please, if you don't like the answer you got the first time asking a question, either _edit_ the question, provide more info, or ask for more feedback, but don't blindly post the same question again. I've voted to close for that reason. I have provided a detailed answer, though... so please: respect my effort and at least read it, try it and provide me with feedback, too — Elias Van Ootegem, Aug 07 '13 at 11:36
I am importing csv file through phpmyadmin and not by any code — Sanjay Rathod, Aug 07 '13 at 11:50
@SanjayRathod: phpMyAdmin can _show_ you the query it generated for you. Look into that query and edit it using one or more suggestions I made. — Elias Van Ootegem, Aug 07 '13 at 11:53

score 1 · Accepted Answer · edited May 23 '17 at 11:44

From what I gather, after updating your question, is that you're actually updating rows using a CSV, and expect the ON UPDATE clause to set the value of your timestamp field to be updated.
Sadly, when loading a CSV into a database you're not updating, but inserting data, and overwriting existing records. At least, when using a LOCAL INFILE, if the INFILE isn't local, the query will produce an error, if it's a local file, these errors (duplicates) will produce warnings and the operation will continue.

If this isn't the case for you, perhaps consider following one of the examples on the doc pages:

LOAD DATA INFILE 'your.csv'
   INTO TABLE tbl
      (field_name1, field_name2, field_name3)
   SET updated = NOW()
FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY ('\n');

Just in case you can't/won't/forget to add additional information, loading a csv int a MySQL table is quite easy:

LOAD DATA
   LOCAL INFILE '/path/to/file/filename1.csv'
INTO TABLE db.tbl
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
(`field_name1`,`field_name2`,`field_name3`)

If you create a table along the lines of:

CREATE TABLE tbl(
   id INT AUTO_INCREMENT PRIMARY KEY, -- since your previous question mentioned auto-increment
   field_name1 VARCHAR(255) NOT NULL PRIMARY KEY, -- normal fields
   field_name2 INTEGER(11) NOT NULL PRIMARY KEY,
   field_name3 VARCHAR(255) NOT NULL DEFAULT '',
    -- when not specified, this field will receive current_timestamp as value:
   inserted TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   -- if row is updated, this field will hold the timestamp of update-time
   updated TIMESTAMP NOT NULL DEFAULT 0
                              ON UPDATE CURRENT_TIMESTAMP
)ENGINE = INNODB
 CHARACTER SET utf8 COLLATE utf8_general_ci;

This query is untested, so please be careful with it, it's just to give a general idea of what you need to do to get the insert timestamp in there.
This example table will work like so:

> INSERT INTO tbl (field_name1, field_name2) VALUES ('foobar', 123);
> SELECT FROM tbl WHERE field_name1 = 'foobar' AND field_name2 = 123;

This will show:

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
|          id         |     field_name1     |     field_name2     |     field_name3     |       inserted      |       updated       |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
|           1         |        foobar       |         123         |                     | 2013-08-07 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

As you can see, because we didn't explicitly insert a value into the last three fields, MySQL used their DEFAULT values. For field_name3, an empty string was used, for inserted, the default was CURRENT_TIMESTAMP, for updated the default value was 0 which, because the field-type is TIMESTAMP is represented by the value 0000-00-00 00:00:00. If you were to run the following query next:

UPDATE tbl
   SET field_name3 = 'an update'
WHERE field_name1 = 'foobar'
  AND field_name2 = 123
  AND id          = 1;

The row would look like this:

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
|          id         |     field_name1     |     field_name2     |     field_name3     |       inserted      |       updated       |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
|           1         |        foobar       |         123         |      an update      | 2013-08-07 00:00:00 | 2013-08-07 00:00:20 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

that's all. Some basics can be found here, on mysqltutorial.org, but best keep the official manual ready. It's not bad once you get used to it.
Perhaps this question might be worth a quick peek, too.

Set timestamp on insert when loading CSV

1 Answers1