如何在mysql上获得两个日期之间的差异天数?

时间：2022-08-03 21:30:53

I need to get the number of days contained within a couple of dates on mysql.

我需要在mysql中包含几个日期的天数。

For example: Check in date 12-04-2010 Check out date 15-04-2010 and the day difference would be 3

例如:Check in date 12-04-2010 Check out date date 15-04-2010, the day difference is 3

5 个解决方案

#1

186  

What about the DATEDIFF function ?

那么DATEDIFF函数呢?

Quoting the manual's page :

引用手册页:

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation

DATEDIFF()返回expr1 - expr2，该值表示从一个日期到另一个日期的天数。expr1和expr2是日期和时间表达式。在计算中只使用值的日期部分


In your case, you'd use :

在你的情况下，你应该使用:

mysql> select datediff('2010-04-15', '2010-04-12');
+--------------------------------------+
| datediff('2010-04-15', '2010-04-12') |
+--------------------------------------+
|                                    3 | 
+--------------------------------------+
1 row in set (0,00 sec)

But note the dates should be written as YYYY-MM-DD, and not DD-MM-YYYY like you posted.

但是请注意，日期应该写成YYYY-MM-DD，而不是像你发布的那样写成dd - mm - yy。

#2

26  

Note if you want to count FULL 24h days between 2 dates, datediff can return wrong values for you.

注意，如果您想要在两个日期之间数满24天，datediff可以为您返回错误的值。

As documentation states:

作为文档:

Only the date parts of the values are used in the calculation.

在计算中只使用值的日期部分。

which results in

这将导致

select datediff('2016-04-14 11:59:00', '2016-04-13 12:00:00')

选择datediff('2016-04-14 11:59:00'， '2016-04-13 12:00:00')

returns 1 instead of expected 0.

返回1，而不是期望的0。

Solution is using select timestampdiff(DAY, '2016-04-13 11:00:01', '2016-04-14 11:00:00'); (note opposite order of arguments comparing to datediff).

解决方案是使用select timestampdiff(DAY， '2016-04-13 11:00:01'， '2016-04-14 11:00:00');(注意与datediff相对的参数顺序)。

Some examples:

一些例子:

  • select timestampdiff(DAY, '2016-04-13 11:00:01', '2016-04-14 11:00:00'); returns 0
  • 选择timestampdiff(DAY， '2016-04-13 11:00:01'， '2016-04-14 11:00:00');返回0
  • select timestampdiff(DAY, '2016-04-13 11:00:00', '2016-04-14 11:00:00'); returns 1
  • 选择timestampdiff(DAY， '2016-04-13 11:00:00'， '2016-04-14 11:00:00');返回1
  • select timestampdiff(DAY, '2016-04-13 11:00:00', now()); returns how many full 24h days has passed since 2016-04-13 11:00:00 until now.
  • 选择timestampdiff(DAY， '2016-04-13 11:00:00'， now());返回从2016-04-13 11:00:00到现在已经经过了整整24小时。

Hope it will help someone, because at first it isn't that obvious why datediff returns values which seems to be unexpected or wrong.

希望它能对某些人有所帮助，因为一开始，datediff返回的值看起来出乎意料或错误，并不是那么明显。

#3

14  

Use the DATEDIFF() function.

使用DATEDIFF()函数。

Example from documentation:

文档的例子:

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
    -> 1

#4

4  

I prefer TIMESTAMPDIFF because you can easily change the unit if need be.

我更喜欢TIMESTAMPDIFF，因为如果需要的话，你可以很容易地改变这个单元。

#5

0  

Get days between Current date to destination Date

从当前日期到目的地日期之间的天数

 SELECT DATEDIFF('2019-04-12', CURDATE()) AS days;

output

输出

days

 335

#1

186  

What about the DATEDIFF function ?

那么DATEDIFF函数呢?

Quoting the manual's page :

引用手册页:

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation

DATEDIFF()返回expr1 - expr2，该值表示从一个日期到另一个日期的天数。expr1和expr2是日期和时间表达式。在计算中只使用值的日期部分


In your case, you'd use :

在你的情况下，你应该使用:

mysql> select datediff('2010-04-15', '2010-04-12');
+--------------------------------------+
| datediff('2010-04-15', '2010-04-12') |
+--------------------------------------+
|                                    3 | 
+--------------------------------------+
1 row in set (0,00 sec)

But note the dates should be written as YYYY-MM-DD, and not DD-MM-YYYY like you posted.

但是请注意，日期应该写成YYYY-MM-DD，而不是像你发布的那样写成dd - mm - yy。

#2

26  

Note if you want to count FULL 24h days between 2 dates, datediff can return wrong values for you.

注意，如果您想要在两个日期之间数满24天，datediff可以为您返回错误的值。

As documentation states:

作为文档:

Only the date parts of the values are used in the calculation.

在计算中只使用值的日期部分。

which results in

这将导致

select datediff('2016-04-14 11:59:00', '2016-04-13 12:00:00')

选择datediff('2016-04-14 11:59:00'， '2016-04-13 12:00:00')

returns 1 instead of expected 0.

返回1，而不是期望的0。

Solution is using select timestampdiff(DAY, '2016-04-13 11:00:01', '2016-04-14 11:00:00'); (note opposite order of arguments comparing to datediff).

解决方案是使用select timestampdiff(DAY， '2016-04-13 11:00:01'， '2016-04-14 11:00:00');(注意与datediff相对的参数顺序)。

Some examples:

一些例子:

  • select timestampdiff(DAY, '2016-04-13 11:00:01', '2016-04-14 11:00:00'); returns 0
  • 选择timestampdiff(DAY， '2016-04-13 11:00:01'， '2016-04-14 11:00:00');返回0
  • select timestampdiff(DAY, '2016-04-13 11:00:00', '2016-04-14 11:00:00'); returns 1
  • 选择timestampdiff(DAY， '2016-04-13 11:00:00'， '2016-04-14 11:00:00');返回1
  • select timestampdiff(DAY, '2016-04-13 11:00:00', now()); returns how many full 24h days has passed since 2016-04-13 11:00:00 until now.
  • 选择timestampdiff(DAY， '2016-04-13 11:00:00'， now());返回从2016-04-13 11:00:00到现在已经经过了整整24小时。

Hope it will help someone, because at first it isn't that obvious why datediff returns values which seems to be unexpected or wrong.

希望它能对某些人有所帮助，因为一开始，datediff返回的值看起来出乎意料或错误，并不是那么明显。

#3

14  

Use the DATEDIFF() function.

使用DATEDIFF()函数。

Example from documentation:

文档的例子:

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
    -> 1

#4

4  

I prefer TIMESTAMPDIFF because you can easily change the unit if need be.

我更喜欢TIMESTAMPDIFF，因为如果需要的话，你可以很容易地改变这个单元。

#5

0  

Get days between Current date to destination Date

从当前日期到目的地日期之间的天数

 SELECT DATEDIFF('2019-04-12', CURDATE()) AS days;

output

输出

days

 335
标签：

相关文章