Page 1 of 1

PHP Date function vs mysql date format functions

Posted: Fri Jan 20, 2006 6:24 pm
by hawleyjr
There have been a few posts lately regarding formatting dates in mysql vs getting the default date and formatting them in PHP. I think it's a personal preference which one you choose, however, this sparked my interest so I wrote this quick benchmark test.

Also, if you have another PHP date function let me know and I'll add it.


First the results:
0.234193086624 seconds for the query. 14,653 Records

0.187507152557 seconds using dateFun1()
Total: 0.421700239182

0.205161094666 seconds using dateFun2()
Total: 0.43935418129

0.24080491066 seconds for the query (Using Mysql date function). 14,653 Records
Total 0.24080491066
0.240545988083 seconds for the query. 14,653 Records

0.191325187683 seconds using dateFun1()
Total: 0.431871175766

0.21414399147 seconds using dateFun2()
Total: 0.454689979553

0.29377913475 seconds for the query (Using Mysql date function). 14,653 Records
Total 0.29377913475
0.251131057739 seconds for the query. 14,653 Records

0.211993932724 seconds using dateFun1()
Total: 0.463124990463

0.213098049164 seconds using dateFun2()
Total: 0.464229106903

0.275920152664 seconds for the query (Using Mysql date function). 14,653 Records
Total 0.275920152664
0.236452102661 seconds for the query. 14,653 Records

0.192218065262 seconds using dateFun1()
Total: 0.428670167923

0.27031493187 seconds using dateFun2()
Total: 0.506767034531

0.250230073929 seconds for the query (Using Mysql date function). 14,653 Records
Total 0.250230073929
And the Code:

Code: Select all

<?php

	mysql_connect ('localhost', $user, $pass);
			
	mysql_select_db ('db_dttm_test');

function getmicrotime() 
{ 
   list($usec, $sec) = explode(" ", microtime()); 
   return ((float)$usec + (float)$sec); 
} 
function dateFun1( $dttm )
{
          $day             = substr($dttm, 8, 2);
          $month             = substr($dttm, 5, 2);
          $year             = substr($dttm, 0, 4);
          $date             = "$month-$day-$year";
          return $date;
} 
function dateFun2( $dttm ){

	return date("m-d-Y", strtotime($dttm)); 	
	
}

$t = getmicrotime();	
	$rslt = mysql_query( 'SELECT id,dttm from db_dttm_test.tbl_dttm_test' );
	
	$aRslt = array();

	while($row = mysql_fetch_array( $rslt )){	
		
		$aRslt[$row['id']] = $row['dttm'];
	}
$t1 = getmicrotime() - $t; 

echo '<h3> ' . $t1 . ' seconds for the query. ' . number_format( count($aRslt) ) . ' Records</h3>'; 


$t = getmicrotime();

	foreach($aRslt as $dttm){
		 dateFun1( $dttm );			
	}

$t = getmicrotime() - $t; 

echo '<h3> ' . $t . ' seconds using dateFun1()</h3> Total: ' . ($t + $t1); 


$t = getmicrotime();

	foreach($aRslt as $dttm){
		 dateFun2( $dttm );
	}

$t = getmicrotime() - $t; 

echo '<h3> ' . $t . ' seconds using dateFun2()</h3> Total: ' . ($t + $t1); 


$t = getmicrotime();	
	$rslt = mysql_query( 'SELECT id,DATE_FORMAT(dttm,"%m-%d-%Y") as dttm from db_dttm_test.tbl_dttm_test' );
	
	$aRslt = array();

	while($row = mysql_fetch_array( $rslt )){	
		
		$aRslt[$row['id']] = $row['dttm'];
	}
$t = getmicrotime() - $t; 


echo '<h3> ' . $t . ' seconds for the query (Using Mysql date function). ' . number_format( count($aRslt) ) . ' Records</h3>'; 





?>
The table (14,653 Records)

Code: Select all

CREATE TABLE `tbl_dttm_test` (
  `id` int(11) NOT NULL auto_increment,
  `dttm` datetime default NULL,
  PRIMARY KEY  (`id`)
) 
//sample data
id,"dttm"
1,"2005-02-17 23:59:35"
2,"2005-02-18 00:03:38"
3,"2005-02-18 00:04:30"
4,"2005-02-18 00:04:48"
5,"2005-02-18 00:05:05"
6,"2005-02-18 00:05:23"
7,"2005-02-18 00:07:14"
8,"2005-02-18 00:10:27"
9,"2005-02-18 00:12:57"
10,"2005-02-18 00:14:23"
11,"2005-02-18 00:14:59"
12,"2005-02-18 00:15:55"
13,"2005-02-18 00:17:46"
14,"2005-02-18 00:17:49"
15,"2005-02-18 00:17:53"
16,"2005-02-18 00:17:55"
17,"2005-02-18 00:17:58"
18,"2005-02-18 00:18:01"
19,"2005-02-18 00:18:05"
20,"2005-02-18 00:18:11"

Posted: Fri Jan 20, 2006 6:32 pm
by josh
.0000034 seconds difference between the methods?


The overhead of using double quotes on your string in the datetime function you wrote is more than that. I'd rather spend my time optimizing my mysql joins, etc..

Posted: Fri Jan 20, 2006 6:34 pm
by hawleyjr
Yes, however it's about 40% longer when using PHP vs the mysql function. :)