Solved

mysql - calculating difference between two times in query

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
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 video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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