使用MySQL查找几天内两个时间戳之间的差异
使用DATEDIFF()MySQL中的函数以天为单位获取两个时间戳之间的时差。
语法如下-
select datediff(yourColumnName1,yourColumnName2) as anyVariableName from yourTableName;
为了理解上述语法，让我们创建一个表。以下是创建表的查询-
mysql> create table DifferenceTimestamp −> ( −> IssueTime timestamp, −> DueTime timestamp −> );
借助insert命令将一些记录插入表中。我们在这里设置日期。查询如下-
mysql> insert into DifferenceTimestamp values(now(),date_add(now(),interval -30 Day)); mysql> insert into DifferenceTimestamp values(now(),date_add(now(),interval -24 Day)); mysql> insert into DifferenceTimestamp values(now(),date_add(now(),interval -5 Day));
在select语句的帮助下显示表中的所有记录。查询如下-
mysql> select *from DifferenceTimestamp;
以下是输出-
+---------------------+---------------------+ | IssueTime | DueTime | +---------------------+---------------------+ | 2018-12-07 17:48:28 | 2018-11-07 17:48:28 | | 2018-12-07 17:48:40 | 2018-11-13 17:48:40 | | 2018-12-07 17:48:46 | 2018-12-02 17:48:46 | +---------------------+---------------------+ 3 rows in set (0.00 sec)
这是获取以天为单位的两个时间戳之间的差异的查询。查询如下-
mysql> SELECT DATEDIFF(IssueTime, DueTime) AS DifferenceInTimestampDays from DifferenceTimestamp;
以下是输出-
+---------------------------+ | DifferenceInTimestampDays | +---------------------------+ | 30 | | 24 | | 5 | +---------------------------+ 3 rows in set (0.00 sec)