How to convert DateTime to a number in MySQL?

To convert the date time to a number in MySQL, the syntax is as follows −

SELECT UNIX_TIMESTAMP(yourColumnName) as anyVariableName FROM yourTableName;

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

mysql> create table DateTimeToNumberDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> releasedDate datetime,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.46 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into DateTimeToNumberDemo(releasedDate) values(now());
Query OK, 1 row affected (0.19 sec)

mysql> insert into DateTimeToNumberDemo(releasedDate) values(curdate());
Query OK, 1 row affected (0.24 sec)

mysql> insert into
DateTimeToNumberDemo(releasedDate) values('1978-01-19');
Query OK, 1 row affected (0.55 sec)

mysql> insert into DateTimeToNumberDemo(releasedDate) values('2016-09-13');
Query OK, 1 row affected (0.46 sec)

mysql> insert into DateTimeToNumberDemo(releasedDate) values('2017-11-12');
Query OK, 1 row affected (0.22 sec)

mysql> insert into DateTimeToNumberDemo(releasedDate) values('2018-12-09');
Query OK, 1 row affected (0.21 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from DateTimeToNumberDemo;

The following is the output −

+----+---------------------+
| Id | releasedDate       |
+----+---------------------+
| 1 | 2019-01-12 21:20:57 |
| 2 | 2019-01-12 00:00:00 |
| 3 | 1978-01-19 00:00:00 |
| 4 | 2016-09-13 00:00:00 |
| 5 | 2017-11-12 00:00:00 |
| 6 | 2018-12-09 00:00:00 |
+----+---------------------+
6 rows in set (0.00 sec)

The following is the query to convert date time to a number −

mysql> select unix_timestamp(releasedDate) as DateToNumber from DateTimeToNumberDemo;

The following is the output −

+--------------+
| DateToNumber |
+--------------+
| 1547308257   |
| 1547231400   |
| 253996200    |
| 1473705000   |
| 1510425000   |
| 1544293800   |
+--------------+
6 rows in set (0.00 sec)