MySQL中两个日期之间的差异

How to calculate the difference between two dates, in the format YYYY-MM-DD hh: mm: ss and to get the result in seconds or milliseconds?

如何计算两个日期之间的差异，以YYYY-MM-DD hh: mm: ss，以秒或毫秒计算结果?

13 个解决方案

#1

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

So, you can use TIMESTAMPDIFF for your purpose.

因此，您可以将TIMESTAMPDIFF用于您的目的。

#2

If you are working with DATE columns (or can cast them as date columns), try DATEDIFF() and then multiply by 24 hours, 60 min, 60 secs (since DATEDIFF returns diff in days). From MySQL:

如果使用日期列(或者可以将它们转换为日期列)，那么尝试DATEDIFF()，然后乘以24小时、60分钟、60秒(因为DATEDIFF以天为单位返回diff)。从MySQL:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

for example:

例如:

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30 00:00:00') * 24*60*60

#3

Get the date difference in days using DATEDIFF

使用DATEDIFF获取日差

SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;
+------+
| days |
+------+
|   17 |
+------+

OR

或

Refer the below link MySql difference between two timestamps in days?

请参考下面的链接MySql在两天内的差异?

#4

SELECT 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')

#5

select 
unix_timestamp('2007-12-30 00:00:00') - 
unix_timestamp('2007-11-30 00:00:00');

#6

Or, you could use TIMEDIFF function

或者，你可以用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'

#7

This function takes the difference between two dates and shows it in a date format yyyy-mm-dd. All you need is to execute the code below and then use the function. After executing you can use it like this

此函数接受两个日期之间的差异，并以日期格式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;

#8

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

-- result : 01:48:00

——结果:01:48:00

OK it's not quite what the OP asked, but it's what I wanted to do :-)

这不是OP要求的，但这是我想做的

#9

This code calculate difference between two dates in yyyy MM dd format.

此代码以yyyyy MM dd格式计算两个日期之间的差异。

declare @StartDate datetime 
declare @EndDate datetime

declare @years int
declare @months int 
declare @days int

--NOTE: date of birth must be smaller than As on date, 
--else it could produce wrong results
set @StartDate = '2013-12-30' --birthdate
set @EndDate  = Getdate()            --current datetime

--calculate years
select @years = datediff(year,@StartDate,@EndDate)

--calculate months if it's value is negative then it 
--indicates after __ months; __ years will be complete
--To resolve this, we have taken a flag @MonthOverflow...
declare @monthOverflow int
select @monthOverflow = case when datediff(month,@StartDate,@EndDate) - 
  ( datediff(year,@StartDate,@EndDate) * 12) <0 then -1 else 1 end
--decrease year by 1 if months are Overflowed
select @Years = case when @monthOverflow < 0 then @years-1 else @years end
select @months =  datediff(month,@StartDate,@EndDate) - (@years * 12) 

--as we do for month overflow criteria for days and hours 
--& minutes logic will followed same way
declare @LastdayOfMonth int
select @LastdayOfMonth =  datepart(d,DATEADD
    (s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0)))

select @days = case when @monthOverflow<0 and 
    DAY(@StartDate)> DAY(@EndDate) 
then @LastdayOfMonth + 
  (datepart(d,@EndDate) - datepart(d,@StartDate) ) - 1  
      else datepart(d,@EndDate) - datepart(d,@StartDate) end 


select
 @Months=case when @days < 0 or DAY(@StartDate)> DAY(@EndDate) then @Months-1 else @Months end

Declare @lastdayAsOnDate int;
set @lastdayAsOnDate = datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate),0)));
Declare @lastdayBirthdate int;
set @lastdayBirthdate =  datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)));

if (@Days < 0) 
(
    select @Days = case when( @lastdayBirthdate > @lastdayAsOnDate) then
        @lastdayBirthdate + @Days
    else
        @lastdayAsOnDate + @Days
    end
)
print  convert(varchar,@years)   + ' year(s),   '  +
       convert(varchar,@months)  + ' month(s),   ' +
       convert(varchar,@days)    + ' day(s)   '   

#10

If you've a date stored in text field as string you can implement this code it will fetch the list of past number of days a week, a month or a year sorting:

如果你在文本框中存储了一个日期作为字符串，你可以实现这段代码，它将获取过去一周的天数列表，一个月或者一年的排序:

SELECT * FROM `table` WHERE STR_TO_DATE(mydate, '%d/%m/%Y') < CURDATE() - INTERVAL 30 DAY AND STR_TO_DATE(date, '%d/%m/%Y') > CURDATE() - INTERVAL 60 DAY

//This is for a month

SELECT * FROM `table` WHERE STR_TO_DATE(mydate, '%d/%m/%Y') < CURDATE() - INTERVAL 7 DAY AND STR_TO_DATE(date, '%d/%m/%Y') > CURDATE() - INTERVAL 14 DAY

//This is for a week 

%d%m%Y is your date format

%d%m%Y是您的日期格式

This query display the record between the days you set there like: Below from last 7 days and Above from last 14 days so it would be your last week record to be display same concept is for month or year. Whatever value you're providing in below date like: below from 7-days so the other value would be its double as 14 days. What we are saying here get all records above from last 14 days and below from last 7 days. This is a week record you can change value to 30-60 days for a month and also for a year.

这个查询将显示您在那里设置的天数之间的记录，如下所示:从最近7天开始，从最近14天开始，因此显示相同概念的记录将是您的最后一周记录，时间为月或年。无论你在以下日期提供了什么样的价值:在7天之后，其他的价值将是它的2倍，14天。我们在这里说的是从过去14天得到所有记录，从过去7天得到所有记录。这是一个星期的记录，你可以改变价值30-60天，一个月，也可以一年。

Thank You Hope it will help someone.

谢谢你希望它能帮助某人。

#11

SELECT TIMESTAMPDIFF(SECOND,'2018-01-19 14:17:15','2018-01-20 14:17:15');

Second approach

第二种方法

SELECT ( DATEDIFF('1993-02-20','1993-02-19')*( 24*60*60) )AS 'seccond';

选择(" 1993-02-20 "，" 1993-02-19 ")*(24*60*60)为" seccond ";

CURRENT_TIME() --this will return current Date
DATEDIFF('','') --this function will return  DAYS and in 1 day there are 24hh 60mm 60sec

#12

You would simply do this:

你可以这么做:

SELECT (end_time - start_time) FROM t; -- return in Millisecond
SELECT (end_time - start_time)/1000 FROM t; -- return in Second

#13

Why not just

为什么不直接

Select Sum(Date1 - Date2) from table

从表中选择Sum(Date1 - Date2)。

date1 and date2 are datetime

date1和date2是datetime