Solved

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

Posted on 2015-02-11
425 Views
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

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
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')

======================================================================
0
Question by:matthewdacruz

LVL 108

Accepted Solution

Ray Paseur earned 500 total points
ID: 40602973
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

Author Comment

ID: 40798364
Thanks for info Ray. Go it worked out
0

## Featured Post

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …