PHP Date function vs mysql date format functions

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

PHP Date function vs mysql date format functions

Post 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"
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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..
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Yes, however it's about 40% longer when using PHP vs the mysql function. :)
Post Reply