如何在格式为YYYY-MM-DD hh:mm:ss
的两个日期之间计算差异,并获得以秒或毫秒表示的结果?
SELECT TIMEDIFF('2007-12-31 10:02:00','2007-12-30 12:01:01');
-- result: 22:00:59, the difference in HH:MM:SS format
SELECT TIMESTAMPDIFF(SECOND,'2007-12-30 12:01:01','2007-12-31 10:02:00');
-- result: 79259 the difference in seconds
因此,您可以使用TIMESTAMPDIFF
来达到您的目的。
TIMEDIFF
的结果乘以24*60*60
不等于TIMESTAMPDIFF
的结果。 - David TuiteTIMEDIFF()
函数期望开始时间和结束时间参数的顺序与 TIMESTAMPDIFF()
函数所期望的相反。 - Mr. Lance E SloanROUND(TIMESTAMPDIFF(HOUR, updated_at, NOW()) / (1)) AS 'Date',
可以给出时间差,单位为小时。ROUND(TIMESTAMPDIFF(SECOND, updated_at, NOW()) / (1)) AS 'Date',
可以给出时间差,单位为秒。ROUND(TIMESTAMPDIFF(SECOND, updated_at, NOW()) / (60 * 60)) AS 'Date',
可以给出时间差,单位为小时。 - Olotin Temitopehttp://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
例如:mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30 00:00:00') * 24*60*60
DATEDIFF
不会返回小数部分。 - Juan Carlos Oropeza使用DATEDIFF获取日期之间的天数差异
SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;
+------+
| days |
+------+
| 17 |
+------+
或者
请参考以下链接:MySql计算两个时间戳之间的天数?
SELECT * FROM table WHERE DATE(database_date) = DATE_SUB(CURDATE(), INTERVAL 3 DAY)
。 - EdwardSELECT TIMESTAMPDIFF(HOUR,NOW(),'2013-05-15 10:23:23')
calculates difference in hour.(for days--> you have to define day replacing hour
SELECT DATEDIFF('2012-2-2','2012-2-1')
SELECT TO_DAYS ('2012-2-2')-TO_DAYS('2012-2-1')
select
unix_timestamp('2007-12-30 00:00:00') -
unix_timestamp('2007-11-30 00:00:00');
select DATEDIFF(now(), '2022-08-12 17:55:51.000000') from properties p WHERE p.property_name = 'KEY';
结果:6
SELECT TIMESTAMPDIFF(SECOND,'2018-01-19 14:17:15','2018-01-20 14:17:15');
第二种方法
SELECT ( DATEDIFF('1993-02-20','1993-02-19')*( 24*60*60) )AS 'seccond';
CURRENT_TIME() --this will return current Date
DATEDIFF('','') --this function will return DAYS and in 1 day there are 24hh 60mm 60sec
或者,您可以使用TIMEDIFF函数
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
'-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001' , '2008-12-30 01:01:01.000002');
'46:58:57.999999'
select TO_CHAR(TRUNC(SYSDATE)+(to_date( '31-MAY-2012 12:25', 'DD-MON-YYYY HH24:MI')
- to_date( '31-MAY-2012 10:37', 'DD-MON-YYYY HH24:MI')),
'HH24:MI:SS') from dual
这个函数计算两个日期之间的差值,并以 yyyy-mm-dd 的日期格式显示出来。您只需要执行下面的代码,然后使用该函数即可。执行后,您可以像这样使用它
SELECT datedifference(date1, date2)
FROM ....
.
.
.
.
DELIMITER $$
CREATE FUNCTION datedifference(date1 DATE, date2 DATE) RETURNS DATE
NO SQL
BEGIN
DECLARE dif DATE;
IF DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2)))) < 0 THEN
SET dif=DATE_FORMAT(
CONCAT(
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 ,
'-',
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 ,
'-',
DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(DATE_SUB(date1, INTERVAL 1 MONTH)), '-', DAY(date2))))),
'%Y-%m-%d');
ELSEIF DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2)))) < DAY(LAST_DAY(DATE_SUB(date1, INTERVAL 1 MONTH))) THEN
SET dif=DATE_FORMAT(
CONCAT(
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 ,
'-',
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 ,
'-',
DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2))))),
'%Y-%m-%d');
ELSE
SET dif=DATE_FORMAT(
CONCAT(
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 ,
'-',
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 ,
'-',
DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2))))),
'%Y-%m-%d');
END IF;
RETURN dif;
END $$
DELIMITER;