Data Type for Unix Timestamp in MySQL

The best data type for unix_timestamp in MySQL is integer. The integer data type is as follows

int(11);

The integer data type is useful for condition checking like ( > ,<= ) and indexing. The return type of unix_timestamp is an integer.

However, let us see what we get as UNIX Timestamp, when we convert datetime to timestamp.

To understand the above concept, let us first create a table. The query to create a table is as follows

mysql> create table UnixTime
   -> (
   -> DueTime datetime
   -> );
Query OK, 0 rows affected (0.55 sec)

Insert records in the form of date using insert command. The query is as follows

mysql> insert into UnixTime values(now());
Query OK, 1 row affected (0.15 sec)

mysql> insert into UnixTime values('2010-10-14');
Query OK, 1 row affected (0.15 sec)

mysql> insert into UnixTime values('2020-09-24');
Query OK, 1 row affected (0.15 sec)

Let us now display all records from the table using select command. The query is as follows

mysql> select *from UnixTime;

The following is the output

+---------------------+
| DueTime             |
+---------------------+
| 2018-12-19 10:07:11 |
| 2010-10-14 00:00:00 |
| 2020-09-24 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)

Let us see the query to convert datetime to UNIX timestamp

mysql> select unix_timestamp(DueTime) as Output from UnixTime;

The following is the output

+------------+
| Output     |
+------------+
| 1545194231 |
| 1286994600 |
| 1600885800 |
+------------+
3 rows in set (0.00 sec)