CODEWITHSUNDEEP
mysql
Zoli
Zoli

Reputation: 1081

MySQL timestamp field does not accept unix_timestamp() result

I have a mysql table with a column named date of TIMESTAMP type. I'm trying to load big amount of data using LOAD DATA LOCAL INFILE. Everything works, except that the date column cant be filled with a custom unix timestamp created from a string. Here is the SQL query:

LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE names FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (@nm) set `name`=@nm, `gender`='female', `date`=UNIX_TIMESTAMP(STR_TO_DATE('2015-06-07 09:21:44', '%Y-%m-%d %H:%i:%s'));

Also tryed this:

LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE names FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (@nm) set `name`=@nm, `gender`='female', `date`=UNIX_TIMESTAMP('2015-06-07 09:21:44');

The problem is, that the date field is filled with 0000-00-00 00:00:00 .

Have anybody any idea what can be the problem?

Upvotes: 0

Views: 1128

Answers (2)

Vatev
Vatev

Reputation: 7590

Try removing the UNIX_TIMESTAMP and STR_TO_DATE function.

TIMESTAMP fields work with the yyyy-mm-dd hh:mm:ss format (same as DATETIME).

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

If date is datatype TIMESTAMP, just do

 `date` = '2015-06-07 09:21:44'

No need for the rigmarole with the STR_TO_DATE and UNIX_TIMESTAMP. MySQL does an implicit conversion of a string literal (in that format) to DATE, DATETIME, TIMESTAMP datatype when in a context that expects one of those datatypes.

You'd want to use UNIX_TIMESTAMP function to return an integer value, if you were storing the value into a numeric column, rather than a TIMESTAMP.

Upvotes: 1

Related Questions