I am working on one project having frontend as PHP and backend MySql with XAMPP server.
I have one field named "created" with datatype as DATETIME in MySQl.I required to pass defauld current datetime value to this field.
I knew that there are some function in PHP to add current datetime to MySql database like
date_default_timezone_set("America/Chicago");
$date = date('Y-m-d H:i:s');
In some case(possible by mistake),PHP code may not pass datetime in mysql and defaul it is taking "0000-00-00 00:00:00" by MySql.
Can any one guide me to what word I need to add as default value for this created field.
I have tried to add CURRENT_TIMESTAMP as default value.
But It give me error :
#1067 - Invalid default value for 'created'
Can anyone give me direction on this.
Thanks.
If you want to use the ON UPDATE CURRENT_TIMESTAMP, you should make the field a TIMESTAMP and not a DATETIME.
Or, consider using a trigger for this situation: http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html
(as slugonamission said in this thread: MySQL is not allowing ON UPDATE CURRENT_TIMESTAMP for a DATETIME field)
Please Change TYPE from DATETIME to timestamp and in DEFAULT select CURRENT_TIMESTAMP
Hope this will work.
Note : You do not need to give length/values
If you want to update your record on any update,change attributes to on update CURRENT_TIMESTAMP
Setting CURRENT_TIMESTAMP as default value is only possible if you have MySQL version 5.6.5 or later. Otherwise you can only set a default value of 0 to a datetime type column.
Example @ MySQL Fiddle 5.5.32
You can try the following example on SQL Fiddle and will learn that it is not supported using MySQL 5.5.32. Change it to 5.6.6 and following example should be working.
Example @ MySQL Fiddle 5.6.6
Example from my local server:
mysql> create table ts( dt datetime default current_timestamp );
Query OK, 0 rows affected (0.54 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.15 |
+-----------+
Refer to MySQL documentation:
Although Mysql solutions have been provided by other users here but if you want to use PHP then make sure all other values of your created column are set to their default values. A simple query like below is sufficient to enter correct values in DATETIME field using PHP. Remove the default value for the created column and make sure you'r query systax is proper.
mysql_query("insert into table (created) values('".$date."')");