CODEWITHSUNDEEP
mysqldate
Pablo Carpio
Pablo Carpio

Reputation: 75

Combine SUB_DATE AND TIMEDIFF to substract 1 hour in mysql

I'm having problems with timediff and DATE_SUB. Here is my MYSQL query:

SELECT id, clock_user_id, clock_date,

(Select clock_time from aura_clock where aura_clock.clock_type = 'Start' and  
 aura_clock.clock_date = t1.clock_date) as start, (Select clock_time 
 from aura_clock where aura_clock.clock_type = 'Stop' 
 and aura_clock.clock_date =   
 t1.clock_date) as Stop,

THE PROBLEM START HERE

TIMEDIFF((select clock_time FROM aura_clock t 
WHERE t.clock_date = t1.clock_date AND t.clock_time > t1.clock_time 
ORDER BY t.clock_time LIMIT 1), MIN(clock_time)) as spent

FROM aura_clock t1 WHERE t1.clock_date >= DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 
WEEKDAY(CURDATE()) DAY),INTERVAL 15 day) 
AND t1.clock_date < DATE_SUB(curdate(),INTERVAL DAYOFWEEK(curdate()) + 6 day)

GROUP BY clock_date

The result is :

enter image description here

Now, I want to subtract 1 hour from the time spent using DATE_SUB but it didn't work.

Upvotes: 0

Views: 174

Answers (1)

jaczes
jaczes

Reputation: 1394

as mirkobrankovic wrote:

((TIMEDIFF((select clock_time FROM aura_clock t 
WHERE t.clock_date = t1.clock_date AND t.clock_time > t1.clock_time 
ORDER BY t.clock_time LIMIT 1), MIN(clock_time))) - INTERVAL 1 HOUR) AS new_spent

should work

EDIT:

The best i got is time in seconds : http://sqlfiddle.com/#!2/18160/66/0

a lot depends on MYSQL version

Upvotes: 2

Related Questions