How do you calculate time difference between two columns in mysql?

Hi Experts

I want to calculate by day how much time was used to do the tasks.
I have the following date time columns

- data_discovered_theme_keywords.drill_start
- data_discovered_theme_keywords.drill_ended

When I run the query below I get the following results

Task completed       Date Actioned
60                                  2015-02-09
99                                  2015-02-10
37                                  2015-02-11

I need to calculate how much time was used in hours and minutes for each day.
So in the first row it will be  60 * Time used = total hour : minutes production

How is this done?

Query
======================================================================

SELECT
Count(data_discovered_theme_keywords.discovered_keyword_id) AS TASKS_Completed,
DATE_FORMAT(data_discovered_theme_keywords.drill_ended, '%Y-%m-%d') AS Date_Actioned

FROM
data_discovered_theme_keywords
WHERE
data_discovered_theme_keywords.drill_level = 1 AND
data_discovered_theme_keywords.drill_completed = 1
Group By DATE_FORMAT(data_discovered_theme_keywords.drill_ended, '%Y-%m-%d')

======================================================================
matthewdacruzAsked:
Who is Participating?
 
Ray PaseurCommented:
We would be able to give a better answer if we could see the complete table definition and some test data (the SSCCE).  But even without that I can give you some of the theory.

Columns that contain date and time information should be defined as DATETIME.  
http://dev.mysql.com/doc/refman/5.7/en/datetime.html

Then you can use both SQL and PHP functions to make date computations.  
http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

The PHP side of things is described in this article, along with several practical examples.
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
matthewdacruzAuthor Commented:
Thanks for info Ray. Go it worked out
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.