MySQL时间戳差异 [英] MySQL Timestamp Difference

问题描述

我有一个带有"timestam"列的表，该表以以下格式存储时间戳:"2012-12-10 21:24:30"

I have a table with column timestam which stores a timestamp with this format: "2012-12-10 21:24:30"

我正在寻找一个采用当前时间戳的SQL查询，并将其与列中的时间戳相减，并以这种格式给出差异:

I am looking for a SQL Query that takes the current timestamp and subtracts it with the one in the column and gives the difference in this format:

剩余3小时2分钟"

寻找可以做到这一点的MySQL查询.

Looking for a MySQL Query that does that.

推荐答案

使用

其中的unit参数，应为以下值之一: 微秒(微秒)，秒，分钟，小时，天，周，月， 季度或年份.

where unit argument, which should be one of the following values: MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

我的方法:将单位设置为 SECOND ，然后使用 SEC_TO_TIME

my approach : set unit as SECOND and then use SEC_TO_TIME

SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND,`table`.`time_column`,CURRENT_TIMESTAMP()))

// will return in hh:mm:ii format

更新

如果HourDifference少于48小时，返回是?否则否

update

Return yes if HourDifference are less than 48 hours? otherwise no

SELECT IF(TIMESTAMPDIFF(HOUR,`time_column`,CURRENT_TIMESTAMP())< 48  ,'yes','no')

这篇关于MySQL时间戳差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！