Mysql default value for Date and Datetime field

Question

Can any one specify, how can be set default value for Date and Datetime field in Mysql from phpmyadmin interface?

Solution 1

Use CURRENT_TIMESTAMP constant

also check this link

Solution 2

You can specify exact default values for DATE and DATETIME fields, e.g. -

CREATE TABLE table1(
  id INT(11) NOT NULL,
  date_column DATE DEFAULT '2012-01-01',
  datetime_column DATETIME DEFAULT '2010-05-05 10:30:00',
  PRIMARY KEY (id)
);

INSERT INTO table1 (id) VALUES (1);

SELECT * FROM table1;

+----+-------------+---------------------+
| id | date_column | datetime_column     |
+----+-------------+---------------------+
|  1 | 2012-01-01  | 2010-05-05 10:30:00 |
+----+-------------+---------------------+

If you want to use function to define DEFAULT values, then you can create a trigger.

Comments

[outis]

Which version of phpMyAdmin? Also, dba.SE is a better fit for this question.

[Vishwanath Dalvi]

You can check here dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.htm‌​l

[Sumesh TG]

Thank you. CURRENT_TIMESTAMP is only for 'timestamp' type, not for 'date' & 'datetime' type.

[Sumesh TG]

Thank you. Can you specify how to set default 'date' as 'current date' and 'datetime' as 'current date time', without using a trigger?.

[Devart]

No, there is no good ways. Unfortunately MySQL doesn't allow using functions to set default values for DATE, DATETIME fields. You also may implement this logic in a stored procedure, and use it to add new records.

[mrsrinivas]

@SumeshTG: FYI - storing date and time, as it is in database not suggestible.