Solved

mysql - calculating difference between two times in query

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL: Get All Duplicates (not grouped) 3 48
Easy to use inventory management software 7 69
join tables 4 50
updating the date data 12 20
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
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.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

914 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

20 Experts available now in Live!

Get 1:1 Help Now