1

How to calculate total hours between now and any date but to exclude weekdays?

I'm trying on this way:

select id, creationTime, 
time_format(timediff(now(), creationTime), '%H:%m:%s') AS totalspenttime 
from tblrezgo where DAYOFWEEK(creationTime) NOT IN (1,7)

This query should remove saturday and sundays from calculation but it seems that includes also those two days.

By running query:

select id, creationTime, DAYOFWEEK(creationTime) FROM tblrezgo

Output is:

+-------------+---------------------+------------+  
| ID          | creationTime        | DAYOFWEEK  |
+-------------+---------------------+------------+  
| 1           | 2015-10-23 17:12:05 |   6        |
+-------------+---------------------+------------+
| 2           | 2015-10-24 10:23:11 |   7        |
+-------------+---------------------+------------+    
| 3           | 2015-10-24 11:51:04 |   7        |
+-------------+---------------------+------------+
| 4           | 2015-10-26 14:30:28 |   2        |
+-------------+---------------------+------------+
| 5           | 2015-10-26 08:24:59 |   2        |
+-------------+---------------------+------------+    
| 6           | 2015-10-26 17:29:03 |   2        |
+-------------+---------------------+------------+
| 7           | 2015-10-27 08:16:45 |   3        |
+-------------+---------------------+------------+

If i run my query then totalspenttime for ID = 1 is about 86 hour which is not correct. I've checked and it should be about 41 hours 'til now (if we execlude two days of weekend).

Ullas
  • 11,450
  • 4
  • 33
  • 50
Josef
  • 2,648
  • 5
  • 37
  • 73
  • 1
    Can you show us some sample output? Could you try `SELECT id, creationTime, DAYOFWEEK(creationTime)` to verify that the query in fact be not working correctly? – Tim Biegeleisen Oct 27 '15 at 07:20
  • 3
    Possible duplicate of [MySQL TimeDiff to exclude weekends](http://stackoverflow.com/questions/13312535/mysql-timediff-to-exclude-weekends) – Shadow Oct 27 '15 at 07:22

0 Answers0