Perhaps as a third alternative you may want to try
X = RIGHT(CAST(time as CHAR(14)),2);
Here is a fourth alternative requiring some project management decisions: Instead of storing date('YmdHis')) you should store date('U')). The U stands for seconds since Jan 1, 1970. Then, your formula would be simpler
X = MOD(time,60);
If you must keep what you have, use the BENCHMARK function
SET @dt=20130416152057;
SELECT SUBSTR(CAST(@dt AS CHAR(14)),9,2);
SELECT MOD(FLOOR(@dt / 10000), 100);
SET @iterations=1000000;
SELECT BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2));
SELECT BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100));
SET @iterations=10000000;
SELECT BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2));
SELECT BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100));
I tried it on a random DB Server 1 millions times and then 10 million times
mysql> SET @dt=20130416152057;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT SUBSTR(CAST(@dt AS CHAR(14)),9,2);
+-----------------------------------+
| SUBSTR(CAST(@dt AS CHAR(14)),9,2) |
+-----------------------------------+
| 15 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT MOD(FLOOR(@dt / 10000), 100);
+------------------------------+
| MOD(FLOOR(@dt / 10000), 100) |
+------------------------------+
| 15 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SET @iterations=1000000;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2));
+----------------------------------------------------------+
| BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2)) |
+----------------------------------------------------------+
| 0 |
+----------------------------------------------------------+
1 row in set (0.15 sec)
mysql> SELECT BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100));
+-----------------------------------------------------+
| BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100)) |
+-----------------------------------------------------+
| 0 |
+-----------------------------------------------------+
1 row in set (0.40 sec)
mysql> SET @iterations=10000000;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2));
+----------------------------------------------------------+
| BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2)) |
+----------------------------------------------------------+
| 0 |
+----------------------------------------------------------+
1 row in set (1.50 sec)
mysql> SELECT BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100));
+-----------------------------------------------------+
| BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100)) |
+-----------------------------------------------------+
| 0 |
+-----------------------------------------------------+
1 row in set (4.02 sec)
mysql>
On the particular hardware I ran this on, your first expression is about 2.67 times faster.
Give it a Try !!!
Best Answer
To resume the situation for people who don’t want to follow the link to the original question on SO: the OP is querying a Mysql DB via an unspecified interface which (sensibly) refuses to show the values stored in
VARBINARYfields with encrypted data, and (rather dumb-mindedly) still shows aBLOBlabel for the value returned by callingAES_DECRYPTon them. The answer given on SO was to cast those results asCHAR(50), which from a DB perspective makes little sense, but in this way the interface shows the results as text and everybody is happy.The question here is: if I have to use
CHAR(50), how can I handle longer values?My answer is: you don’t say which interface you are using (your screenshots are not enough for me to recognize it), but I’d bet that if you cast as
CHARinstead ofCHAR(50)it still works and you have no size limits that you should care of. So:However, this is an interface question, not a DB engine question, and it is rather strange. I suspect the interface settings have been changed from the ones used when asking the first question, because now the strings which the interface thinks are binary are automatically displayed as
HEX. Giving the first query to a commandline interface does not produce the results shown here. To get them from a commandline interface I have to write the second field asHEX(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512)))); similarly, the supposedly wrong result of the second query is simplyHEX('test')instead of'test'because the interface does not know it is actually readable text. Casting it toCHARshould work.Please be aware that all those conversions the interface is forcing you to perform are not needed when data is exchanged between different parts of your application.