PHP ==> MYSQL how can i add the date and time in my database?

Question

im trying to put NOW() as the default value for the datetime field type ="datetime" but im getting this error

ALTER TABLE `members` ADD `datetime` DATETIME DEFAULT 'NOW()' NOT NULL

MySQL said:
#1067 - Invalid default value for 'datetime'

Answer 1

Firstly off 'now()' isn't a function call, it's a string therefore you CANNOT use it for a default value.

You have to insert a value upon insertion to that table withe the now() string.

Look Carefully about the datatypes of different Time Structures
==============================

--
-- Lets use the test Database
--
mysql> use test;
Database changed

--
-- Lets create a Table (temp for this example)
--
mysql> create table a (
-> x date,
-> y timestamp,
-> z datetime);
Query OK, 0 rows affected (0.02 sec)

--
-- Lets insert only at column x so rest are default
--
mysql> insert into a(x) values(now());
Query OK, 1 row affected (0.00 sec)

--
-- Lets insert only at column y so rest are default
--
mysql> insert into a(y) values(now());
Query OK, 1 row affected (0.00 sec)

--
-- Lets insert only at column z so rest are default
--
mysql> insert into a(z) values(now());
Query OK, 1 row affected (0.00 sec)

--
-- Lets Select everything in that table
--
mysql> select * from a;
+------------+---------------------+---------------------+
| x | y | z |
+------------+---------------------+---------------------+
| 2006-06-15 | 2006-06-15 00:42:17 | NULL |
| NULL | 2006-06-15 00:42:23 | NULL |
| NULL | 2006-06-15 00:42:26 | 2006-06-15 00:42:26 |
+------------+---------------------+---------------------+
3 rows in set (0.00 sec)
==============================

Now notice what happened... TIMESTAMP by default updates the current time..., The others are null. So change your datatype to be TIMESTAMP if you care so much about the default value.

Answer 2

Use TIMESTAMP instead of DATETIME, make it NOT NULL but do not set a default, and it will automatically fill in with the current date.

Note that if you have multiple fields of type TIMESTAMP, this will only work with the first field.

Answer 3

Use the Curtime() function instead