减去日期并列出MySQL中的日期

There are two rows in the table created and modified. I need to subtract a modified - created and list the days(given in result table).

表中有两行已创建和修改。我需要减去修改后的 - 创建并列出日期(在结果表中给出)。

Table name : user

表名:用户

|-----|---------------------|---------------|
| id  |       created       |   modified    |
|-----|---------------------|---------------|
| 1   | 2013-04-12 17:49:26 |  2013-04-16   |
| 2   | 2013-04-12 20:20:25 |  2013-04-26   |
| 3   | 2013-04-12 12:24:23 |  2013-04-12   |
| 4   | 2013-04-18 19:03:47 |  2013-04-23   |
|-------------------------------------------|

I need the result like this given below..

我需要如下所示的结果..

|-----|----------------|
| id  |    days        |
|-----|----------------|
| 1   |     4          |
| 2   |     14         |
| 3   |     0          |
| 4   |     5          |
|----------------------|

How to do that?

怎么做?

4 个解决方案

#1

You can use DateDiff() function for this:

您可以使用DateDiff()函数:

SELECT ID, Datediff (modified,created) AS days
FROM user

Output:

╔════╦══════╗
║ ID ║ DAYS ║
╠════╬══════╣
║  1 ║    4 ║
║  2 ║   14 ║
║  3 ║    0 ║
║  4 ║    5 ║
╚════╩══════╝

See this SQLFiddle

#2

Suppose both created and modified are datetime type:

假设创建和修改都是datetime类型:

SELECT id, DATEDIFF(modified, created) as days FROM result

#3

You can try this:

你可以试试这个:

SELECT id, Datediff(modified,created) as days FROM user

#4

You can use DateTime::diff in php to do this. http://www.php.net/manual/en/datetime.diff.php

您可以在php中使用DateTime :: diff来执行此操作。 http://www.php.net/manual/en/datetime.diff.php

You want to use DateTime class also. http://www.php.net/manual/en/class.datetime.php

您还想使用DateTime类。 http://www.php.net/manual/en/class.datetime.php

In mysql use DATEDIFF() function https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

在mysql中使用DATEDIFF()函数https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff