Comparison of php date () and SQL FROM_UNIXTIME () Efficiency

Let's take a look at the efficiency of php date () and SQL FROM_UNIXTIME (). In the end, the performance is better. Let's look at the instance together.

In php, there are two methods to convert the int type Timestamp to date and time. One is to use the date ("Y-m-d H: I ", time () for conversion; also, FROM_UNIXTIME (add_time," % Y-% m-% d % H: % m ") for conversion in SQL, I usually don't know much about it.

To understand the efficiency and difference between them, I made an instance. First, a table is created and only two fields are added. One is the self-increasing id and the other is the int type time. After adding the two fields, insert into t1 (add_time) using the self-replication statement) select add_time from t1 quickly copies more than 0.1 million data records to test.

The program is very simple, that is, compare the conversion execution time in mysql and in php.

I have executed each piece of code many times, and I have taken a relatively moderate period of time from it. Now we can clearly see that the FROM_UNIXTIME () function is used in the database than the date () function of php () functions are much more efficient, but we cannot ignore the execution overhead of mysql databases. Therefore, FROM_UNIXTIME () is fast without considering the database overhead.

PHP source code reference:

The Code is as follows:

Copy code

<? Php Header ("Content-type: text/html; charset = UTF-8 "); // Program running time $ Starttime = explode ('', microtime ());   /* · The following code area ·········*/ $ Link = mysql_connect ("localhost", "root", "root "); Mysql_select_db ("test "); Mysql_query ("set names utf8 "); $ SQL = "select add_time from t1 limit 100000 "; $ Res = mysql_query ($ SQL, $ link ); $ Num = mysql_num_rows ($ res ); While ($ row = mysql_fetch_array ($ res )){ // Echo date ("Y-m-d H: I", $ row ['add _ time']). "| "; } /* · The above is the code area ·········*/   // Program running time $ Endtime = explode ('', microtime ()); $ Thistime = $ endtime [0] + $ endtime [1]-($ starttime [0] + $ starttime [1]); $ Thistime = round ($ thistime, 3 ); Echo '   //-------------------------------------------------------- //-------------------------------------------------------- // Program running time $ Starttime = explode ('', microtime ());   /* · The following code area ·········*/ $ SQL = "select add_time from t1 limit 100000 "; $ Res = mysql_query ($ SQL, $ link ); $ Num = mysql_num_rows ($ res ); While ($ row = mysql_fetch_array ($ res )){ Echo $ row ['add _ time']. "| "; } /* · The above is the code area ·········*/   // Program running time $ Endtime = explode ('', microtime ()); $ Thistime = $ endtime [0] + $ endtime [1]-($ starttime [0] + $ starttime [1]); $ Thistime = round ($ thistime, 3 ); Echo '   //-------------------------------------------------------- //-------------------------------------------------------- // Program running time $ Starttime = explode ('', microtime ());   /* · The following code area ·········*/ $ SQL = "select FROM_UNIXTIME (add_time, '% Y-% m-% d % H: % m') as add_time from t1 limit 100000 "; $ Res = mysql_query ($ SQL, $ link ); $ Num = mysql_num_rows ($ res ); While ($ row = mysql_fetch_array ($ res )){ // Echo $ row ['add _ time']. "| "; } /* · The above is the code area ·········*/ // Program running time $ Endtime = explode ('', microtime ()); $ Thistime = $ endtime [0] + $ endtime [1]-($ starttime [0] + $ starttime [1]); $ Thistime = round ($ thistime, 3 ); Echo ' ?>