Pretty Time

From Hashmysql
Jump to: navigation, search

DROP FUNCTION IF EXISTS prettyTime;

CREATE FUNCTION prettyTime(inTime TIME) RETURNS varchar(255)
RETURN ELT(  
	1 + INTERVAL(TIME_TO_SEC(inTime), 60, 3600, 86400, 2678400), 
	CONCAT(TIME_TO_SEC(inTime), ' seconds'), 
	CONCAT(TIME_TO_SEC(inTime) / 60, ' minutes'), 
	CONCAT(TIME_TO_SEC(inTime) / 3600, ' hours'), 
	CONCAT(TIME_TO_SEC(inTime) / 86400, ' days'), 
	CONCAT(TIME_TO_SEC(inTime) / 2678400, ' months') 
);

mysql> SELECT prettyTime('00:00:15');
+------------------------+
| prettyTime('00:00:15') |
+------------------------+
| 15 seconds             |
+------------------------+
1  row in set (0.00 sec)
mysql> SELECT prettyTime('00:10:15');
+------------------------+
| prettyTime('00:10:15') |
+ ------------------------+
| 10.2500 minutes        |
+------------------------+
1 row in set (0.00 sec)
 mysql> SELECT prettyTime('20:10:15');
+------------------------+
| prettyTime('20:10:15') |
+------------------------+
| 20.1708 hours          |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT prettyTime('40:10:15');
+------------------------+
| prettyTime('40:10:15') |
+------------------------+
| 1.6738 days            |
+------------------------+
1  row in set (0.00 sec)
mysql> SELECT prettyTime('800:10:15');
+-------------------------+
| prettyTime('800:10:15') |
+-------------------------+
| 1.0755 months           |
+-------------------------+
1 row in set (0.00 sec)