30

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?

shgnInc
  • 2,054
  • 1
  • 23
  • 34
Maximus
  • 2,906
  • 4
  • 35
  • 55
  • There's nothing wrong with your `UNIX_TIMESTAMP` usage, as you can see by doing `SELECT ` under mysql. Does your table have more than one column? Or perhaps the data type for the column is something other than int (because that's what `UNIX_TIMESTAMP` returns)? – SaganRitual Aug 06 '13 at 23:48
  • Try entering your `INSERT` statements into mysql and see how it responds. – SaganRitual Aug 06 '13 at 23:54
  • @GreatBigBore My field is timestamp, it's not a date and time column and there is only one column – Maximus Aug 07 '13 at 00:16

1 Answers1

38

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.

SaganRitual
  • 3,143
  • 2
  • 24
  • 40
  • That worked! Thanks. I am wondering why strtotime php functions works fine but not UNIX_TIMESTAMP in mysql query? When I pass unix_timestamp value in date function it shows me 43 years back date. – Maximus Aug 07 '13 at 00:15
  • It's probably because the PHP functions you're using are expecting something other than the int that is returned by `UNIX_TIMESTAMP`. Check the documentation carefully and you'll probably find the problem. – SaganRitual Aug 07 '13 at 00:22