the type is integer like :

int(11) 

is good for indexing and conditions like > < =

Unix time_t is either 32 bits wide, or 64. So, int(8) or binary(8) is sufficient, at least for the next 293 billion years.

Usually it does not matter whether you use TIMESTAMP or DATETIME datatype.

• In older versions, TIMESTAMP was 4 bytes and DATETIME was 8.
• Think of DATETIME as a picture of a clock; think of TIMESTAMP as an instant in time, worldwide. That is, if you connect to the same database, but from a different timezone, a DATETIME will look the the same, but a TIMESTAMP will be adjusted for timezone.
• NOW(), SELECTing into PHP, etc, are compatible with both.
• Both are externally seen as a string, such as '2015-04-25 17:09:01'.
• Since TIMESTAMP is stored as a 32-bit integer (but you don't see that), it is limited to ~1970-2038.
• Since DATETIME is clock time, there will be a missing/extra hour twice a year if you switch to/from daylight savings time.

Yes, you could use UNIX_TIMESTAMP() and have an INT UNSIGNED, but wouldn't it be better to see '2015-...'? (That would be 4 bytes.)

A Unix timestamp is a large integer (the number of seconds since 1970-01-01 00:00:00 UTC), so INT(11) is the correct datatype.

Unfortunately, I don't think there's any way to specify a default that will insert the current timestamp. You'll need to call UNIX_TIMESTAMP() explicitly when inserting, and use that. Function calls aren't allowed in DEFAULT specifications.

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)

Standard UNIX timestamps are a signed 32bit integer, which in MySQL is a regular "int" column. There's no way you could store 9,999,999,999, as that's way outside the representation range - the highest a 32bit int of any sort can go is 4,294,967,295. The highest a signed 32bit in goes is 2,147,483,647.

If/when UNIX timestamps go to a 64bit data type, then you'll have to use a MySQL "bigint" to store them.

As for int(10), the (10) portion is merely for display purposes. MySQL will still use a full 32bit internally to store the number, but only display 10 whenever you do a select on the table.

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Also, as of MySQL 8.0.19 the DATETIME supports time zone offsets, so there's even less reason to use TIMESTAMP now.