MySQL MySQL时间戳差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13813675/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Timestamp Difference
提问by Sohel Mansuri
I have a table with column timestam which stores a timestamp with this format: "2012-12-10 21:24:30"
我有一个带有列时间戳的表，它存储具有以下格式的时间戳：“2012-12-10 21:24:30”
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:
我正在寻找一个 SQL 查询，它采用当前时间戳并将其与列中的时间戳相减，并给出以下格式的差异：
"3 Hours and 2 mins Remaining"
《还剩 3 小时 2 分钟》
Looking for a MySQL Query that does that.
正在寻找执行此操作的 MySQL 查询。
回答by diEcho
use TIMESTAMPDIFF
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
where unit argument, which should be one of the following values: MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
其中 unit 参数应为以下值之一：MICROSECOND（微秒）、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 或 YEAR。
my approach :set unit as SECONDand then use SEC_TO_TIME
我的方法：将单位设置为SECOND，然后使用SEC_TO_TIME
SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND,`table`.`time_column`,CURRENT_TIMESTAMP()))
// will return in hh:mm:ii format
update
更新
Return yesif HourDifferenceare less than 48 hours? otherwise no
如果少于 48 小时，则返回是HourDifference？否则没有
SELECT IF(TIMESTAMPDIFF(HOUR,`time_column`,CURRENT_TIMESTAMP())< 48 ,'yes','no')
回答by Fathah Rehman P
Assume your table name is 'testtable'
假设你的表名是'testtable'
Table create query is given below
表创建查询如下
CREATE TABLE `testtable` (
`id` INT(2) NOT NULL AUTO_INCREMENT,
`period` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
Insert some data to test my query. Query to insert some data is given below
插入一些数据来测试我的查询。下面给出了插入一些数据的查询
INSERT INTO `testtable` (`id`, `period`) VALUES
(1, '2012-12-10 17:21:09'),
(2, '2012-11-06 18:21:12'),
(3, '2012-12-06 18:21:18'),
(4, '2012-12-06 19:21:24'),
(5, '2012-12-06 18:21:27');
Now execute following query to get your answer
现在执行以下查询以获得您的答案
SELECT *,
CONCAT(HOUR(difftime), ' hours ', MINUTE(difftime), ' Minutes ',
SECOND(difftime), ' seconds remaining') AS timetaken
FROM (SELECT *,
SEC_TO_TIME(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(
ttable.period)) AS
diffTime
FROM testtable ttable) AS temptable1
Output is given below
输出如下
The column 'timetaken' will display answer.
'timetaken' 列将显示答案。
