PHP date() format when inserting into datetime in MySQL

Question

Whats the correct format using the date() function in PHP to insert into a MySQL datetime type column?

I've been trying this date("Y-M-D G:i:s"); but that just inserts "0000-00-00 00:00:00" everytime.

Answer 1

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.

Answer 2

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.

Answer 3

Try

date("Y-m-d h:i:s")

See all the options for date() in the manual.

Answer 4

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 |
+---------------------+