Solved

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

Posted on 2015-02-11
2
431 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
2 Comments
 
LVL 108

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
phpmyadmin 3 33
Calculating number of days between two dates 7 32
PHP: Insert Data into MySQL 5 13
Checking CSRF token within a function 36 7
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now