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

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

1. Identify some of the drawback of implementing sql server triggers

2. Using Triggers

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.

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 row
begin
  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)