Solved

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

Posted on 2015-02-11
2
495 Views
Last Modified: 2015-05-27
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')

======================================================================
0
Comment
Question by:matthewdacruz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 110

Accepted Solution

by:
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

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

Featured Post

WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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 …

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question