MySQL timestamp field does not accept unix_timestamp() result

Question

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?

MySQL doesn't have a UNIX_TIMESTAMP data type, do you mean TIMESTAMP ? — Vatev
– Vatev, Commented Jul 7, 2015 at 14:37
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). — Vatev
– Vatev, Commented Jul 7, 2015 at 14:40
@Vatev, could you move your comment to an answer? You provided it first and it works. — Zoli
– Zoli, Commented Jul 7, 2015 at 14:43

Vatev · Accepted Answer · 2015-07-07 14:46:12Z

1

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).

answered Jul 7, 2015 at 14:46

Vatev

7,5801 gold badge36 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

spencer7593 · Accepted Answer · 2015-07-07 14:45:18Z

1

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.

edited Jul 7, 2015 at 14:45

answered Jul 7, 2015 at 14:41

spencer7593

109k15 gold badges122 silver badges148 bronze badges

1 Comment

Zoli Over a year ago

Your answer is right, thanks for the help. However, @Vatev 's comment came first, I need to give him the acceptance. Thanks anyway for the help!

Collectives™ on Stack Overflow

MySQL timestamp field does not accept unix_timestamp() result

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related