0

New to this forum, but hopefully someone can help.

I am using a table which has 3 columns i'm looking to use:

LOGOFF_REASON
START_TIME
END_TIME

I'm looking to get the duration between START_TIME & END_TIME (ideally in seconds, but in any format will be fine) when a particular reason code is populated in LOGOFF_REASON - 'Lunch' for example.

I've tried a few ways, but getting some odd results back so wondered if anyone could add some assistance.

How results look in a table:

enter image description here

Tomislav Stankovic
  • 3,080
  • 17
  • 35
  • 42
Stuart Murray
  • 1
  • 1
  • Check http://stackoverflow.com/questions/3528219/mysql-how-to-get-the-difference-between-two-timestamps-in-seconds – AlecTMH Nov 24 '16 at 14:30

1 Answers1

0

Try this:

SELECT TIME_TO_SEC(TIMEDIFF(END_TIME, START_TIME)) 
  FROM TABLENAME
 WHERE LOGOFF_REASON = 'Lunch' ;

UPDATE

Following your comment, you can try this:

SELECT SUM(TIME_TO_SEC(TIMEDIFF(END_TIME, START_TIME)))
  FROM TABLENAME
 WHERE LOGOFF_REASON = 'Lunch' ;

Hope this fully satisfies your needs.

UPDATE 2

The following will give you the accumulated time for each reason:

SELECT LOGOFF_REASON , SUM(TIME_TO_SEC(TIMEDIFF(END_TIME, START_TIME)))
  FROM TABLENAME
 GROUP BY LOGOFF_REASON ;
FDavidov
  • 3,505
  • 6
  • 23
  • 59
  • Thanks FDavidov, I should have said that there are a load of where clauses in already and the Select section would need to be a sumif Was using this, but I'm sure there will be a better way of doing it sum(if(sessions.logoff_reason = 'Lunch',time_to_sec(timediff(if(sessions.end_time<>'0000-00-00 00:00:00',sessions.end_time,NOW()),sessions.start_time)),NULL)) AS Lunch, – Stuart Murray Nov 24 '16 at 16:13
  • Thanks again. What I really need to do is have it as a sum(if as there are about 10 different states other than 'lunch' so can't use it in there where statement as it would then ignore other states – Stuart Murray Nov 25 '16 at 09:49
  • Done. It is custom in SO to up-vote answers that help you and/or solve your problem (and mark them as THE answer by clicking on the "V" at the left of the answer). – FDavidov Nov 25 '16 at 10:03