MySQL 如何将DateTime转换为数字

要将日期时间转换为数字，在MySQL中的语法如下-

SELECT UNIX_TIMESTAMP(yourColumnName) as anyVariableName FROM yourTableName;

为了理解以上语法，让我们创建一个表。创建表的查询语句如下-

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

使用insert命令在表中插入一些记录。查询语句如下-

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)

使用select语句显示表中所有记录。查询如下-

mysql> select *from DateTimeToNumberDemo;

以下是输出内容-

+----+---------------------+
| 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)

以下是将日期时间转换为数字的查询语句-

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

以下是输出内容-

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

阅读更多：MySQL 教程