CODEWITHSUNDEEP
mysqldatediff
John Joe
John Joe

Reputation: 12803

Explain this query in mysql

Can someone explain this query to me?

SELECT OPEN_DATETIME,
  DATEDIFF(OPEN_DATETIME, '2016-12-13 23:59:59') AS Aging
FROM batch
WHERE DATEDIFF(OPEN_DATETIME,'2016-12-14 23:59:59')>20;

Output

+------------------------+-------+
| OPEN_DATETIME          | Aging |
+------------------------+-------+
| 2017-01-26 16:28:41    |    44 |
| 2017-01-25 16:32:48    |    43 |
| 2017-01-27 02:00:00    |    45 |
+------------------------+-------+

I understand datediff() mean to get the number of difference days between two dates on MySQL. But I just don't understand why it needs two datediff() in this query?

Upvotes: 1

Views: 99

Answers (2)

Eric
Eric

Reputation: 10626

I belive this should work without a double diff, just use HAVING on the alias.

SELECT OPEN_DATETIME,
DATEDIFF(OPEN_DATETIME, '2016-12-13 23:59:59') AS Aging
FROM batch
HAVING Aging >20;

Upvotes: 2

Matt
Matt

Reputation: 15071

The second part of the query:

SELECT OPEN_DATETIME, DATEDIFF(OPEN_DATETIME, '2016-12-13 23:59:59') AS Aging
FROM batch
WHERE DATEDIFF(OPEN_DATETIME,'2016-12-14 23:59:59')>20;

In the WHERE clause:

DATEDIFF(OPEN_DATETIME,'2016-12-14 23:59:59')>20;

Uses the DATEDIFF() function again to limit results where it is over 20 days as you can't use Aging > 20 as the alias is only defined in the query.

To use the Aging alias, and only use DATEDIFF() once, you could use:

SELECT a.* FROM (
SELECT OPEN_DATETIME, DATEDIFF(OPEN_DATETIME, '2016-12-13 23:59:59') AS Aging
FROM batch) a
WHERE a.Aging > 20

EDIT

Or as Eric answered you can use the HAVING clause to select an alias.

Upvotes: 1

Related Questions