Re: How do I check hours between two date/times ?
Posted: Sun Jun 01, 2014 11:43 am
1. You can calculate the time difference in SQL with TIMESTAMPDIFF().
2. Regarding GROUP BY, imagine how you would do it manually. Given a list of items to be grouped by K, you would take the each item in order and see if its K matches one you have already found. If K is new, you keep the item. If K is known, you discard the item. So it's the first item found that will be included in the grouped list. To get the last found item, you need to put the list in reverse order before comparing the items. In SQL, you do that with a subquery.
3. Something to study:
2. Regarding GROUP BY, imagine how you would do it manually. Given a list of items to be grouped by K, you would take the each item in order and see if its K matches one you have already found. If K is new, you keep the item. If K is known, you discard the item. So it's the first item found that will be included in the grouped list. To get the last found item, you need to put the list in reverse order before comparing the items. In SQL, you do that with a subquery.
3. Something to study:
Code: Select all
SELECT *, TIMESTAMPDIFF(HOUR, dateupdated, CURRENT_TIMESTAMP) AS age_hours
FROM table
# +----+--------+---------------------+-----------+-----------+
# | id | ticket | dateupdated | updatedby | age_hours |
# +----+--------+---------------------+-----------+-----------+
# | 1 | 1 | 2014-05-01 00:00:00 | customer | 754 |
# | 2 | 1 | 2014-05-02 00:00:00 | admin | 730 |
# | 3 | 3 | 2014-05-03 00:00:00 | customer | 706 |
# | 4 | 3 | 2014-05-03 00:00:00 | admin | 706 |
# | 5 | 3 | 2014-05-04 00:00:00 | customer | 682 |
# | 6 | 6 | 2014-05-05 00:00:00 | customer | 658 |
# | 7 | 7 | 2014-05-29 00:00:00 | customer | 82 |
# | 8 | 7 | 2014-05-30 00:00:00 | admin | 58 |
# | 9 | 7 | 2014-05-31 00:00:00 | customer | 34 |
# | 10 | 10 | 2014-05-31 00:00:00 | customer | 34 |
# +----+--------+---------------------+-----------+-----------+
SELECT t.*, TIMESTAMPDIFF(HOUR, t.dateupdated, CURRENT_TIMESTAMP) AS age_hours
FROM (SELECT id FROM table ORDER BY dateupdated DESC) s
JOIN table t ON t.id = s.id
GROUP BY ticket
HAVING t.updatedby = 'customer'
AND age_hours > 48
# +----+--------+---------------------+-----------+-----------+
# | id | ticket | dateupdated | updatedby | age_hours |
# +----+--------+---------------------+-----------+-----------+
# | 5 | 3 | 2014-05-04 00:00:00 | customer | 682 |
# | 6 | 6 | 2014-05-05 00:00:00 | customer | 658 |
# +----+--------+---------------------+-----------+-----------+