MySQL MySQL中两个日期之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4759248/
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
Difference between two dates in MySQL
提问by GeoGo
How to calculate the difference between two dates, in the format YYYY-MM-DD hh: mm: ssand to get the result in seconds or milliseconds?
如何以格式计算两个日期之间的差异YYYY-MM-DD hh: mm: ss并以秒或毫秒为单位获得结果？
回答by Devid G
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 TIMESTAMPDIFFfor your purpose.
因此，您可以TIMESTAMPDIFF根据自己的目的使用。
回答by kvista
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:
如果您正在使用 DATE 列（或可以将它们转换为日期列），请尝试 DATEDIFF() 然后乘以 24 小时 60 分 60 秒（因为 DATEDIFF 以天为单位返回差异）。来自 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
回答by Kailas
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 两个时间戳之间的天数差异？
回答by Romancha KC
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')
回答by ajreal
select
unix_timestamp('2007-12-30 00:00:00') -
unix_timestamp('2007-11-30 00:00:00');
回答by abdul rehman kk
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';
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
回答by A.Aleem11
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 天，因此另一个值将是 14 天的两倍。我们在这里所说的是获取过去 14 天以上和过去 7 天以下的所有记录。这是一周记录，您可以将值更改为一个月和一年的 30-60 天。
Thank You Hope it will help someone.
谢谢希望它会帮助某人。
回答by Mohan Kumar
This code calculate difference between two dates in yyyy MM dd format.
此代码以 yyyy 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) '
回答by enor
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;
回答by Guy
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 所要求的，但这是我想做的 :-)
