How to calculate mysql time difference between unix timestamp column and now() format
Calculating the time difference between the following timestamp format mysql column and current time:
Code:
mysql> SELECT created_on FROM `account`;
+------------+
| created_on |
+------------+
| 1369986975 |
+------------+
1 row in set (0.00 sec)
Code:
mysql> SELECT TIME_TO_SEC(TIMEDIFF(NOW(), FROM_UNIXTIME(`created_on`))) AS `time_diff` FROM `account`;
+-----------+
| time_diff |
+-----------+
| 799 |
+-----------+
1 row in set (0.00 sec)
This calculates the difference in seconds between current time and unix timestamp column. Obviously, to turn this into minutes or hours:
Code:
mysql> SELECT TIME_TO_SEC(TIMEDIFF(NOW(), FROM_UNIXTIME(`created_on`))) / 60 AS `time_diff` FROM `account`;
+-----------+
| time_diff |
+-----------+
| 14.0500 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT TIME_TO_SEC(TIMEDIFF(NOW(), FROM_UNIXTIME(`created_on`))) / 3600 AS `time_diff` FROM `account`;
+-----------+
| time_diff |
+-----------+
| 0.2358 |
+-----------+
1 row in set (0.00 sec)