Default Current Date in MySQL 5.0

Archived Topics
1

How would you default the current date in MySQl 5.0 Table Designer?

2

Believe it or not, MySQL doesn’t support dynamic column defaults. That means no default of NOW() or CURRENT_TIMESTAMP.

You just have to specify the current time (again, using NOW() or CURRENT_TIMESTAMP) every time you insert a row.

Very annoying. See discussing here lists.evolt.org/archive/Week-of- … 06065.html

Hope this helps,

3

One small note: now that mysql supports triggers, you can set up this kind of default behavior with something like:

DELIMITER ! CREATE TRIGGER tbl_date_update BEFORE INSERT ON table FOR EACH ROW BEGIN SET NEW.datecolumn = now(); END; !

To be clear here, I set DELIMITER = ! at the start so that I can type the necessary “;” into the mysql command line program without it stopping and throwing an error. The final “!” tells it to actually run the command.

So, this creates a trigger, which will magically set the date column to now() each time a row is inserted.

Hope that helps!

4

This may be stating the obvious, but the way to solve this if FactoryPMI screens are INSERTING records is to use NOW() or CURRENT_TIMESTAMP in your insert statement.

5

Create a column with a datatype “timestamp” and a default value “current_timestamp”.

6

Hey, you’re right! this is new functionality - this wasn’t true in older versions of MySQL. This snippet is taken from the MySQL docs on the TIMESTAMP column type (found here: dev.mysql.com/doc/refman/5.0/en/timestamp.html)

Thanks for the tip!