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

Question

I'm trying to do this, but it seems like MySQL isn't allowing me. Is there a solution to this issue or am I expected to always include the function in my INSERT queries?

CREATE TABLE foo(   created INT NOT NULL DEFAULT UNIX_TIMESTAMP() ) 

I'm aware of the TIMESTAMP type that accepts a CURRENT_TIMESTAMP default, but my client insisted on using epoch time in the database.

Answer 1

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) 

Answer 2

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.