Search code examples
mysqldatetimetimestampunix-timestamp

How to Insert custom date into mysql timestamp field?

I have tried to insert date and time string formatted into mysql timestamp field by using following two methods but both shows me 0000-00-00 00:00:00

INSERT INTO test VALUES ( UNIX_TIMESTAMP('2013-08-05 18:19:03') )
INSERT INTO test VALUES ( UNIX_TIMESTAMP(STR_TO_DATE('2013-08-05 18:19:03', '%Y-%m-%d %H:%i:%s')) )

I believe first one should work as I am expecting but not sure why isn't parsing date and time?

Solution

  • The problem is that your field is defined as TIMESTAMP but UNIX_TIMESTAMP returns an int. Use INSERT INTO test VALUES ('2013-08-05 18:19:03' ) instead.