在MySQL中,使用UNIX_TIMESTAMP()函数将日期时间转换为整数。语法如下:SELECT UNIX_TIMESTAMP(yourDatetimeColumnName) as anyVariableName FROM yourTableName;
为了理解上述语法,让我们创建一个表。创建表的查询如下:mysql> create table DatetimeToInteger
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> ArrivalTime datetime,
-> PRIMARY KEY(Id)
-> );
使用insert命令在表中插入一些记录。查询如下:mysql> insert into DatetimeToInteger(ArrivalTime) values(now());
mysql> insert into DatetimeToInteger(ArrivalTime) values(curdate());
mysql> insert into DatetimeToInteger(ArrivalTime) values('2017-09-21 13:10:55');
mysql> insert into DatetimeToInteger(ArrivalTime) values(date_add(now(),interval 3 year));
使用select语句显示表中的所有记录。查询如下:mysql> select *from DatetimeToInteger;
以下是输出:+----+---------------------+
| Id | ArrivalTime |
+----+---------------------+
| 1 | 2019-01-22 15:12:45 |
| 2 | 2019-01-22 00:00:00 |
| 3 | 2017-09-21 13:10:55 |
| 4 | 2022-01-22 15:14:32 |
+----+---------------------+
4 rows in set (0.00 sec)
这是将日期时间转换为整数的查询:mysql> select unix_timestamp(ArrivalTime) as DateTimeToIntegerDemo from DatetimeToInteger;
以下是输出:+-----------------------+
| DateTimeToIntegerDemo |
+-----------------------+
| 1548150165 |
| 1548095400 |
| 1505979655 |
| 1642844672 |
+-----------------------+
4 rows in set (0.00 sec)