Solved

mysql - calculating difference between two times in query

Posted on 2012-04-08
3
428 Views
Last Modified: 2012-04-09
0 down vote favorite
share [g+] share [fb] share [tw]
      

I know I could use PHP to do this, but wanted to find out if there was a way to calculate the difference between two times using just a query? I tried the query below, but it's returning NULL for the time difference.

The data in my table is stored as:

| created    | changed    |
+------------+------------+
| 1333643004 | 1333643133 |

Open in new window


I wanted to figure out a way to return:

| 2012-04-05 09:23:24 | 2012-04-05 09:25:33 |     00:02:09 |

Open in new window

I tried:

SELECT 
    FROM_UNIXTIME(created) AS created, 
    FROM_UNIXTIME(changed) AS changed, 
    TIMEDIFF ( changed, created ) / 60 AS timediff 
FROM content
    WHERE id = 45;

Open in new window


Which yielded:

| 2012-04-05 09:23:24 | 2012-04-05 09:25:33 | NULL |

Open in new window

0
Comment
Question by:n00b0101
[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
3 Comments
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 37822266
When I tried that, FROM_UNIXTIME is ignored in the TIMEDIFF statement.  TIMEDIFF uses the data from the columns and ignores the 'AS' variables.  Plus I think you had the order wrong in that statement.  And you shouldn't use function names as variable names.

SELECT
    created, changed, (TIMEDIFF ( created, changed ) / 60) AS thediff
FROM content
    WHERE id = 45;
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37822275
timediff requires its inputs to be both datetime or both date datatype.  So it should be:

select timediff(from_unixtime(changed), from_unixtime(created)) from content where id=45;
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37822294
Note also that timediff returns the value in TIME format (hh:mm:ss) so no need to divide by 60.  If you need your result to be something like 1.23 minutes use time_to_sec() (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_time-to-sec) on the output of timediff then divide by 60.
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

690 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