How to set default value for mysql column as unix_timestamp?

Question

I was thinking of something like this:

`post_modified` int(11) NOT NULL DEFAULT UNIX_TIMESTAMP (NOW ()) ON UPDATE UNIX_TIMESTAMP (NOW ()), 

But this code does not work.

Answer 1

Note. I have no idea about MYSQL TRIGGER performance

Follow these links

• Identify some of the disadvantages of deploying trigger SQL servers

• Using triggers

You can create triggers for this.

to insert

CREATE TRIGGER {trigger_name} BEFORE INSERT ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());

to update

CREATE TRIGGER {trigger_name} BEFORE UPDATE ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());

Answer 2

It is impossible to use it, as you showed.

From the doc data type in MySql :

"The default value must be constant, it cannot be a function or expression. This means, for example, that you cannot set the default value for the date column as a function value, such as NOW () or CURRENT_DATE. The exception is that you you can specify CURRENT_TIMESTAMP as the default value for the TIMESTAMP column. "

See the answer below for a workaround:

Is it possible to create a column with UNIX_TIMESTAMP by default in MySQL?

Hope this helps!

Answer 3

You can create triggers for this.

To insert

 CREATE TRIGGER {trigger_name} BEFORE INSERT ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW()); 

To update

 CREATE TRIGGER {trigger_name} BEFORE UPDATE ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());