// you’re reading...

Featured

Creating a MySQL Function to convert miles and kilometers

MySQL has the ability to create user defined functions that can be called just like the built-ins. There was a recent project that involved some distance calculations. As part of the performance testing, we experimented with moving the logic around to different parts of the application stack. Here’s a short example of two functions created to convert miles to kilometers and vice versa.

mysql> CREATE FUNCTION kilometersFromMiles( n DECIMAL(20,6) ) RETURNS DECIMAL(20,6) DETERMINISTIC
-> COMMENT 'Function to convert miles to kilometers given 1 miles = 1.609344 kilometers'
-> RETURN (1.609344 * n);
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT kilometersFromMiles(10) AS km;
+-----------+
| km        |
+-----------+
| 16.093440 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE FUNCTION milesFromKilometers( n DECIMAL(20,6) ) RETURNS DECIMAL(20,6) DETERMINISTIC
-> COMMENT 'Function to convert kilometers to miles given 1 miles = 1.609344 kilometers'
-> RETURN (n / 1.609344);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT milesFromKilometers(10) AS "mi.";
+----------+
| mi.      |
+----------+
| 6.213712 |
+----------+
1 row in set, 1 warning (0.01 sec)

But hey… what’s that warning?!?!

mysql> SHOW WARNINGS;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1265 | Data truncated for column 'mi.' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)

The DECIMAL data type precision throws a warning in this case. No biggie. We aren’t worried about precision greater than six digits. 🙂

Discussion

No comments for “Creating a MySQL Function to convert miles and kilometers”

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/

%d bloggers like this: