Solved

mysql - calculating difference between two times in query

Posted on 2012-04-08
3
424 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
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

777 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