How to Insert custom date into mysql timestamp field?

mysql datetime timestamp unix-timestamp
Maximus picture Maximus · Aug 7, 2013 · Viewed 60.6k times · Source

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?

Answer

SaganRitual picture SaganRitual · Aug 7, 2013

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.

Related questions

MySQL convert date string to Unix timestamp

How do I convert the following format to unix timestamp? Apr 15 2012 12:00AM The format I get from DB seems to have AM at the end. I've tried using the following but it did not work: CONVERT(DATETIME, Sales.SalesDate, 103) AS …

Read More
Should I use the datetime or timestamp data type in MySQL?

Would you recommend using a datetime or a timestamp field, and why (using MySQL)? I'm working with PHP on the server side.

Read More
CURRENT_TIMESTAMP in milliseconds

Is there any way to get milliseconds out of a timestamp in MySql or PostgreSql (or others just out of curiosity)? SELECT CURRENT_TIMESTAMP --> 2012-03-08 20:12:06.032572 Is there anything like this: SELECT CURRENT_MILLISEC --> 1331255526000 or the …

Read More