Subtract 2 timestamps for each entry

[Kyias]

Hey guys. 

 

Im very new to PostgreS, always been a MYSql fan. But a package we are using uses Post so i guess its time to learn.

 

What i require is a query wchich subtracts for example "closedon and createdon" then returnes this information into an array which i can then process in a for loop in php.

 

I have had an attempt but no avail.

 

// Query database
	$monthago = time() - ($days * 24 * 60 * 60);

	// TESTING with the Arrays ForEach!

	$value = pg_exec("SELECT closedon - createdon FROM tbl_issues WHERE status LIKE 4 AND createdon NOT LIKE closedon AND project LIKE 0 AND createdon > " . $monthago);
	$value2 = pg_fetch_row($value);
	print_r ($value2); //More testing to make sure values are there


	foreach ($value2 as $value1) { 
echo "working<br />"; // For test purposes 
}

 

"closedon" and "createdon" are the two time stamps which are in the database which need subtracting and then processing one at a time. Ignore everything after WHERE, this all works as it is from a previous query to make sure ceretain values are set.

 

Thank you

[Kyias]

Just to update.

 

This does work. But is only returning 1 array variable and is not returning ALL of the results i would like it to.

Array ( [0] => 2667 ) 

It should be showing a large array with 50+ values within

[F1Fan]

I am unfamiliar with the "pg_exec" function, and I can't find any documentation on it. If you know MySQL, you can do most of the same thing with the same functions, but replace "mysql_" with "pg_" for your functions. But, to make your code work, replace "pg_exec" with "pg_query" and replace "pg_fetch_row" (which only fetches ONE row) with "pg_fetch_all" (which fetches ALL rows).

[Kyias]

F1Fan,

 

From what i am aware pg_exec is a defined function by the help desk system which we are currently using. I did notice this function was not pre-defined on the PHP website and i have also never seen it before.

 

I am used to Mysql butr the system is a pre-made package the company has been using for a while and i am just merely adapting it. So to do a whole migration would be very large and not worth while.

 

That function works, but it only returns one row like you said. *click* ooooh. let me try what you said and i will get back to you.

 

[Kyias]

Just to make it easier to understand for everyone.

 

	$value = pg_exec("SELECT closedon - createdon FROM tbl_issues WHERE status LIKE 4 AND createdon NOT LIKE closedon AND project LIKE 0 AND createdon > " . $monthago);
	$value2 = pg_fetch_row($value);
	print_r ($value2); //More testing to make sure values are there

 

This only returns one value in the array when it should return all of the values in the database. If no body has any ideas ill persaveer.

[F1Fan]

...*click* ooooh. let me try what you said and i will get back to you.

Um, it's probably because you're still using pg_fetch_row rather than pg_fetch_all. What happened to trying my suggestion?!

[Kyias]

F1Fan,

 

Thanks for the info. Below is the full working script. It hert my brain but i fixed it.

 

	// Query database
	$monthago = time() - ($days * 24 * 60 * 60);

	// TESTING with the Arrays ForEach!

	$value = pg_exec("SELECT * FROM tbl_issues WHERE status LIKE 4 AND createdon NOT LIKE closedon AND project LIKE 0 AND createdon > " . $monthago);


	//array_push($timeArray, pg_fetch_row($value)); // push all of the values into an array
	$i = 0; // Set $i

	for($row = 0; $row < pg_num_rows($value); $row++) {

		$closed = pg_result($value, $row, 'closedon'); // timestamp it was closed
		$created = pg_result($value, $row, 'createdon'); // timestamp it was created



		$timeTook = $closed - $created; 

		// MAKE SURE DAYS ARE ONLY 8.5 HOURS


			// If its greater than 24 hours
			if (isset($timeTook) && $timeTook >= 86400) {

				// Divide the number by 24 hours to find out how many days
				$daysAmmount = ($timeTook / 86400);

				// Work out the ammount of hours to subtract
				$hoursToSubtract = (55800 * $daysAmmount);

				// Subtract the subtraction hours from the total hours available
				$calDays = $timeTook - $hoursToSubtract;

			} else if ($timeTook >= 30600 &&  $timeTook <= 86400 ) {

				//If the time it took to close a job it between 8.5 and 24 hours then set it to 8.5
				// for the job to be closed
				$calDays = 85000;

			} else if ($timeTook <= 30600) {

				//if the time it took to close a job is less than 8.5 hours then no need
				// to calcualte the times
				$calDays = $timeTook;
			}


		//$timeTook = (($timeTook/60)/60);
		// TAKES AWAY WEEKENDS IF THEY ARE THERE
		while ($calDays > 0) {

			// 55800	= 15.5 Hours!
			// 604800 	= 168 Hours!
			// 432000 	= 120 Hours!
			// 86400 	= 24 Hours!
			// 30600	= 8.5 Hours!

			//echo $calDays. "<br />";
			if (isset($calDays) && $calDays >= 604800) {

				$calDays = $calDays - 604800; // Take away the full week
				//$totalTime = $totalTime + 120000; // turn it intop 5 days
				$totalTime[$i] = $totalTime[$i] + 432000;

				$i++;

			} elseif ($calDays >= 432000 && $calDays <= 604800) {

				$sum1 = $calDays - 432000; // Find out how much hours are left after 5 days
				$sum2 = 432000 - $sum1; // Take the ammount in $sum1 from 5 days of hours

				$calDays = $calDays - $sum2;
				//$totalTime = $totaltTime + $sum2; // turn it intop 5 days
				$totalTime[$i] = $totalTime[$i] + $sum2;

				$i++;

			} elseif ($calDays <= 432000) {

				$totalTime[$i] = $totalTime[$i] + $calDays;
				$calDays = $calDays - $calDays;
				//$totalTime = $totalTime + $calDays;


				$i++;

			}

		}



	}


	$average = array_sum($totalTime) / count($totalTime);
	//echo "<br /> Aver Job Completion Time: " . $average . "<br />";



	//echo " TOTAL HOURS AFTER EXPANDED: " . $totalTime . "<br />";


	// Put the value into format	
	// Handle errors
	if (!$average) {

		echo 'Query failed';

	} else {

		$time = $average;

			if ($time < 60) {

				$seconds = floor($time);

				echo $seconds . ' seconds';

			} elseif ($time < 3600) {

				$minutes = floor(($time / 60));
				$seconds = floor($time - ($minutes * 60));

				echo $minutes . ' minute(s) and ' . $seconds . ' second(s)';

			} else {

				$hours = floor((($time / 60) / 60));
				$minutes = floor((($time / 60) - ($hours * 60)));

				echo $hours . ' hour(s) and ' . $minutes . ' minute(s)';

			} #else {

				#$days = floor(((($time / 60) / 60) / 24));
				#$hours = floor(((($time / 60) / 60) - ($days * 24)));

				#echo $days . ' day(s) and ' . $hours . ' hour(s)';

			#}

	}	

	 ?>

[JDBurnZ]

i think you can actually use the sum() function in your query....

 

coredbQuery("SELECY SUM(closedon - openedon) FROM stores WHERE store_id = 1");

[freelance programmer]

You can use existing function in postgresql to calculate diff .