PHP date() format when inserting into datetime in MySQL

I use the following PHP code to create a variable that I insert into a MySQL DATETIME column.

$datetime = date_create()->format('Y-m-d H:i:s');

This will hold the server's current Date and Time.

The problem is that you're using 'M' and 'D', which are a textual representations, MySQL is expecting a numeric representation of the format 2010-02-06 19:30:13

Try: date('Y-m-d H:i:s') which uses the numeric equivalents.

edit: switched G to H, though it may not have impact, you probably want to use 24-hour format with leading 0s.

From the comments of php's date() manual page:

<?php $mysqltime = date ('Y-m-d H:i:s', $phptime); ?>

You had the 'Y' correct - that's a full year, but 'M' is a three character month, while 'm' is a two digit month. Same issue with 'D' instead of 'd'. 'G' is a 1 or 2 digit hour, where 'H' always has a leading 0 when needed.

Here's an alternative solution: if you have the date in PHP as a timestamp, bypass handling it with PHP and let the DB take care of transforming it by using the FROM_UNIXTIME function.

mysql> insert into a_table values(FROM_UNIXTIME(1231634282));
Query OK, 1 row affected (0.00 sec)

mysql> select * from a_table;

+---------------------+
| a_date              |
+---------------------+
| 2009-01-10 18:38:02 |
+---------------------+