Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a field in a mysql database where dates are stored as BIGINT like YYYYMMDDHHMMSS (PHP: date('YmdHis')). I'm building a query, where I check the amount of rows per hour and have tried two different things:

SELECT x hour, ... FROM table WHERE ... GROUP BY x ...

  1. x = SUBSTR(CAST(time AS CHAR(14)), 9, 2)
  2. x = MOD(FLOOR(time / 10000), 100)

I built a quick benchmark (did 1000 querys with each version) and both of them seem to be equally quick so I'm really interested in what database designers would advise me to do?

share|improve this question

migrated from stackoverflow.com Apr 16 '13 at 19:44

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

up vote 2 down vote accepted

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 !!!

share|improve this answer
    
Thank you so much for your detailled answer! Highly appreciated! Unfortunately the project has been running for some years now, they started with date('YmdHis') and I'm afraid a change is no option ... Personally I prefer DATETIME anyways ... Still, again: THANK YOU! :) –  Prince Cherusker Apr 16 '13 at 21:08
add comment

Database designers would definitely advise you to use proper data format and built-in date calculation functions

share|improve this answer
    
Sadly I don't have any influence on that ... –  Prince Cherusker Apr 16 '13 at 8:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.