0

So I have an activitylog column in review table Table screenshot with the contents similar to <u>2016-11-16 15:56:15</u> - Sent by author to LIGHT <br /><u>2016-11-17 07:31:15</u> - Reviewed and forwarded to ALL <br /><u>2016-12-17 11:28:31</u> - Submitted to final destination. in each row. One entry in activity log screenshot

I have no idea how to find the average datediff between the time for "Sent by author" and the time for "Submitted to final destination" for all the users (the rows). Not all the activity logs have the "Submitted to final destination" as some have yet to do so. Any suggestions or help would be great. Sorry if this question was confusing in any sense I'm sort of new to asking questions on stackoverflow.

evkyee
  • 1
  • 3

1 Answers1

0

Here is one method:

select str_to_date(substring_index(r.activitylog, '</u>', 1), 19), '%Y-%m-%d %H:%i:%s') as sent_time,
       (case when r.activitylog like '%Submitted to final destination'
             then str_to_date(left(right(r.activitylog, 57), 19), '%Y-%m-%d %H:%i:%s')
        end) as submit_time
from review r;

(Note: The "57" is a reasonable estimate, but might be a little off.)

Then you can do what you want with the values -- datediff(), avg()` or whatever

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • wait would it be from activitylog? I ask this because activitylog is a column containing all users activity logs in my review table. – evkyee Jun 26 '17 at 19:14