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.

Answer 3

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

Answer 4

As ktretyak's answer, with MySQL v8+, you can do that using a parentheses.

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

After some inserts, you can see it works correctly.
(Tested on MySQL 8.0.26 + only text color patched)

Alternatively you can use an expression, too.
Check ktretyak's answer.

I write this answer instead of comment or edit answer(tried and rejected)
because of lack of REPUTATION and using a parentheses is good solution now.

Answer 5

Well, if MySQL won't let you do it directly, you can always do it using a BEFORE INSERT... FOR EACH ROW trigger.

Answer 6

Now, with MySQL v8+, you can to do this just in parentheses:

CREATE TABLE t1 (
  -- literal defaults
  i INT         DEFAULT 0,
  c VARCHAR(10) DEFAULT '',
  -- expression defaults
  f FLOAT       DEFAULT (RAND() * RAND()),
  b BINARY(16)  DEFAULT (UUID_TO_BIN(UUID())),
  d DATE        DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
  p POINT       DEFAULT (Point(0,0)),
  j JSON        DEFAULT (JSON_ARRAY())
);

See docs