mysql - calculating difference between two times in query

mysql, time

Solution

The result returned by `TIMEDIFF()` is limited to the range allowed for TIME values. Alternatively, you can use either of the functions `TIMESTAMPDIFF()` and `UNIX_TIMESTAMP()`, both of which return integers.

I would call `UNIX_TIMESTAMP()` on both columns (which returns integers) and then subtract them. This will give you an integer which you can convert in the query or in PHP.

SELECT 
    UNIX_TIMESTAMP(created) AS created, 
    UNIX_TIMESTAMP(changed) AS changed, 
    changed-created AS difference
FROM content
    WHERE id = 45;

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

Problem

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 | ``` I wanted to figure out a way to return: ``` | 2012-04-05 09:23:24 | 2012-04-05 09:25:33 | 00:02:09 | ``` I tried: ``` SELECT FROM_UNIXTIME(created) AS created, FROM_UNIXTIME(changed) AS changed, TIMEDIFF ( changed, created ) / 60 AS timediff FROM content WHERE id = 45; ``` Which yielded: ``` | 2012-04-05 09:23:24 | 2012-04-05 09:25:33 | NULL | ```

Original source