I was thinking at something like this:
`post_modified` int(11) NOT NULL DEFAULT UNIX_TIMESTAMP (NOW ()) ON UPDATE UNIX_TIMESTAMP (NOW ()),
But this code it is not working.
A column's default value is part of its definition, but can be modified separately from other aspects of the definition. To change a default value, use ALTER col_name SET DEFAULT : ALTER TABLE mytbl ALTER j SET DEFAULT 1000; Default values must be constants.
UNIX_TIMESTAMP() : This function in MySQL helps to return a Unix timestamp. We can define a Unix timestamp as the number of seconds that have passed since '1970-01-01 00:00:00'UTC. Even if you pass the current date/time or another specified date/time, the function will return a Unix timestamp based on that.
For numeric types, the default is 0 , with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence. For date and time types other than TIMESTAMP , the default is the appropriate “zero” value for the type.
You can use the timestamp column as other posters mentioned. Here is the SQL you can use to add the column in: ALTER TABLE `table1` ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; This adds a column called 'lastUpdated' with a default value of the current date/time.
Note : I have no idea about the performance of MYSQL TRIGGER
Please go through these links
Identify some of the drawback of implementing sql server triggers
Using Triggers
You can create triggers for this.
for insertion
CREATE TRIGGER {trigger_name} BEFORE INSERT ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());
for update
CREATE TRIGGER {trigger_name} BEFORE UPDATE ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());
Nope you cant use it as you showed.
From Data Type doc on MySql:
"The default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column."
Look at below answer for workaround:
Is it possible to create a column with a UNIX_TIMESTAMP default in MySQL?
Hope this will help !
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With