Is it possible to create a column with a UNIX_TIMESTAMP default in MySQL?
The way MySQL implements the TIMESTAMP data type, it is actually storing the epoch time in the database. So you could just use a TIMESTAMP column with a default of CURRENT_TIMESTAMP and apply the UNIX_TIMESTAMP() to it if you want to display it as an int:
CREATE TABLE foo( created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);insert into foo values (current_Date()),(now());select unix_timestamp(created) from foo;+-------------------------+| unix_timestamp(created) |+-------------------------+| 1300248000 || 1300306959 |+-------------------------+2 rows in set (0.00 sec)However, if you really want the datatype of the column to be INT, you can use R. Bemrose's suggestion and set it via trigger:
CREATE TABLE foo( created INT NULL);delimiter $$create trigger tr_b_ins_foo before insert on foo for each rowbegin if (new.created is null) then set new.created = unix_timestamp(); end if;end $$delimiter ;insert into foo values (unix_timestamp(current_Date())), (null);select created from foo;+------------+| created |+------------+| 1300248000 || 1300306995 |+------------+2 rows in set (0.00 sec)
From the documentation:
With one exception, 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.
You can create triggers for this.
for insertion
- query
CREATE TRIGGER {trigger_name} BEFORE INSERT ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());
- in this case
CREATE TRIGGER my_trigger_name_1 BEFORE INSERT ON foo FOR EACH ROW SET new.created = UNIX_TIMESTAMP(NOW());
for update
- query
CREATE TRIGGER {trigger_name} BEFORE UPDATE ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());
- in this case
CREATE TRIGGER my_trigger_name_2 BEFORE UPDATE ON foo FOR EACH ROW SET new.created = UNIX_TIMESTAMP(NOW());
Note : I have no idea about the performance of MYSQL TRIGGER
Please go through these links