check for difference in dates, only choose last 30 days

something like:

SELECT AVG(TIMESTAMPDIFF(HOUR,transDTE, trnPrcDte)) AS difference FROM tblinfo1 where DATEDIFF(transDTE,CURDATE()) <= 30 ORDER BY transDTE

thanks Barry62,

from what you can see, does my statement seem correct?  The result doesn't seem to change based on changing the date range even though we have over a year of data.

Yes, your statement seems to be correct, but to limit it to 30 days, use the where clause I suggested.  however, leave off the 'order by' clause.  I just realized that you want one result, not multiple.

SELECT AVG(TIMESTAMPDIFF(HOUR,transDTE, trnPrcDte)) AS difference FROM tblinfo1 where DATEDIFF(transDTE,CURDATE()) <= 30

ok, it just seems really weird that i get the same exact result no matter the date range i specify.  seems with hundreds of rows that have varying transaction dates and time differences that changing the range would affect the average.

Let me experiment a bit later tonight.  I'm at work, and I can't test it right now.

Are you examining two different dates? I think the TIMESTAMPDIFF gets you the difference in the time stamps but does not account for change in days. In other words, it won't say 24 hours difference if it is the same time for a different day. I will double check the facts on that and post back.

EDIT: never mind. I am not sure why I thought that. It should work as you have it, so it would be interesting to see the data. Maybe the average between the times are consistent because of the nature of the application.

Almost seems as though it's only taking days into account and not hours, mins, sec of the time stamp.

You might want to read this article.  It's more toward the PHP side of things, but you can make PHP and MySQL play together very well.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

To get today's date in ISO-8601 format, suitable for use with MySQL DATE and DATETIME data types, use date('c').

To get 30 days in the past, use date('c', strtotime('Today - 30 days')).

You can also use "-1 Month" but the number of days in a month is ambiguous.  PHP usually returns an intuitively correct value, but not always.

Would there be a difference between finding the dates in PHP vs. at the server level in terms of speed and effeciency?

There is probably no discernible difference.  You might want to use PHP microtime() before and after to compare the speed.  Unless you're doing millions of queries, you may not be able to tell the difference with your wristwatch.

Thanks Ray,

I guess I'm a little confused then on how to setup the math to find the average using just PHP.  I don't quite get how i can get a the info i need to process the equation without pulling some big array or something.

How many rows of data do you expect to have in the results set?  Also, if you can post the CREATE TABLE statements so I can see the column names and definitions it might be helpful to getting a code sample together.

Here is the setup of the table.

SELECT `transID`, `transPayeeID`, `transAmnt`, `transDte`, `bdgtLine`, `pmntType`, `memo`, `groupID`, `userID`, `trnsProcessed`, `chkProcessed`, `trnPrcDte` FROM `tblinfo1` WHERE 1

Select allOpen in new window

trnsProcessed = auto timestamp when information was submitted by user

trnPrcDte = auto timestamp when we processed the information submitted.

in a 30 day period, there are currently less than 100 rows being processed in this script.

thanks Ray,

There was a parenthesis missing in line 12, however I am also now running into the following errors.  I'm troubleshooting myself as well.

Notice:  Undefined variable: mysqli in C:\wamp\www\axessDB\newDB\admin_inc\portal-admin-functions.php on line 75



Fatal error:  Call to a member function query() on a non-object in C:\wamp\www\axessDB\newDB\admin_inc\portal-admin-functions.php on line 75

Yea, I miss the parentheses a lot when I use compound function calls like that.

Please post the entire script now, obscuring only the passwords, so we can see how you're testing the instantiation of the MySQLi object.  We need to see something where the line numbers in the error messages match the line numbers in the code.

you bet.

i have a file that is all functions that I am using throughout the site.

Your code starts on line 47

<?php 
// functions for the admin dashboard
// 1. gets total number of clients
// 2. gets all client fees - Add fees for monthly income.


// get total number of clients
function countChurches() {

$query = "SELECT COUNT(grpName) FROM tblgroup WHERE tblgroup.grpActive = 1 ";
$countResult = mysql_query($query) or die(mysql_error()); 

while ($row = mysql_fetch_array($countResult)) {
	Echo "Current Active Churches = ".$row['COUNT(grpName)']."";
}
}
?>

<?php
// get total amount of fees current
function getRevenue() {
$query = "SELECT SUM(grpMonFee) FROM tblgroup WHERE tblgroup.grpActive = 1 ";
$feeResult = mysql_query($query) or die(mysql_error()); 

while ($row = mysql_fetch_array($feeResult)) {
	$number = number_format($row['SUM(grpMonFee)']);
	Echo "Current Monthly Revenue = $".$number."";
}
}
?>

<?php 
// get length of time between submission to processing

function getProcessTime() {
	
	$query = "SELECT AVG(TIMESTAMPDIFF(HOUR,transDTE, trnPrcDte)) AS difference FROM tblinfo1 where DATEDIFF(transDTE,CURDATE()) <= 10 ORDER BY transDTE";
	$timeResult = mysql_query($query) or die(mysql_error()); 
	
// display the results from getting average time to process transactions	
	$row = mysql_fetch_array($timeResult);
		echo "<h2>Company Stats</h2><p>It currently takes an average of <span style=\"color:#ff9900;font-weight: bold;\">".$row['difference']." hours </span>to process transactions</p>";
			
}
?>

<?php // RAY_temp_axessjosh.php
error_reporting(E_ALL);
echo '<pre>';

/* COLUMNS IN QUESTION
trnsProcessed = auto timestamp when information was submitted by user
trnPrcDte = auto timestamp when we processed the information submitted.
*/

// THE STARTING AND ENDING BOUNDARIES
$new = date('c');
$old = date('c', strtotime('Today - 1 month'));

// MAYBE A USEFUL QUERY
$sql = "
SELECT
  trnsProcessed AS a
, trnPrcDte     AS z
FROM tblinfo1
WHERE ( trnsProcessed BETWEEN '$new' AND '$old' )
AND   ( trnPrcDte     BETWEEN '$new' AND '$old' )
"
;
// ASSUME MYSQLI 
$res = $mysqli->query($sql);
if (!$res) die ($mysqli->error);

// PROCESS THE RESULTS SET
$kount = 0;
$total = 0;
while ($row = $mysqli->fetch_assoc())
{
    // IF THE VALUES COME BACK AS DATATIME STRINGS, CONVERT TO SECONDS
    $a = strtotime($row['a']);
    $z = strtotime($row['z']);
    
    // COMPUTE THE DIFFERENCE IN SECONDS
    $d = $z - $a;
    
    // SUM OF SECONDS
    $total = $total + $d;
    
    // SUM OF MEASUREMENTS
    $kount++;
}

// AVERAGE NUMBER OF SECONDS
$avgs = $total / $kount;

// AVERAGE NUMBER OF DAYS
$days = $avgs / (60*60*24);

echo "WE PROCESSED "
. number_format($kount)
. " TRANSACTIONS IN AN AVERAGE LAG TIME OF "
. number_format($days)
. " DAYS"
;
?>

Select allOpen in new window

I hate to be picky, but the error message says line 75.  However in the code posted here, line 75 is an assignment statement.  So we did not get the actual code.  Things like that waste everybody's time.  When you get a moment learn about the SSCCE.

It looks like the script is still using the old MySQL extension.  Take note of the large red warning box on this page:
http://php.net/manual/en/function.mysql-connect.php

You're going to want to switch to MySQLi or PDO.  For now maybe we can get the query working with something like this.

<?php // RAY_temp_axessjosh.php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);

/* ***************************************************
 *
 *   PUT YOUR DATA BASE CONNECT/SELECT CODE HERE
 *
 *   PUT YOUR TIMEZONE SETTINGS HERE
 *
 */

/* COLUMNS IN QUESTION
trnsProcessed = auto timestamp when information was submitted by user
trnPrcDte = auto timestamp when we processed the information submitted.
*/

// THE STARTING AND ENDING BOUNDARIES
$new = date('c');
$old = date('c', strtotime('Today - 1 month'));

// MAYBE A USEFUL QUERY
$sql = "
SELECT
  trnsProcessed AS a
, trnPrcDte     AS z
FROM tblinfo1
WHERE ( trnsProcessed BETWEEN '$new' AND '$old' )
AND   ( trnPrcDte     BETWEEN '$new' AND '$old' )
"
;
// ASSUME MYSQL INSTEAD OF MYSQLI
$res = mysql_query($sql);
if (!$res) die ("FAIL: $sql BECAUSE: " . mysql_error());

// PROCESS THE RESULTS SET
$kount = 0;
$total = 0;
while ($row = mysql_fetch_assoc())
{
    // IF THE VALUES COME BACK AS DATETIME STRINGS, CONVERT TO SECONDS
    $a = strtotime($row['a']);
    $z = strtotime($row['z']);

    // COMPUTE THE DIFFERENCE IN SECONDS
    $d = $z - $a;

    // SUM OF SECONDS
    $total = $total + $d;

    // SUM OF MEASUREMENTS
    $kount++;
}

// AVERAGE NUMBER OF SECONDS
$avgs = $total / $kount;

// AVERAGE NUMBER OF DAYS
$days = $avgs / (60*60*24);

echo "WE PROCESSED "
. number_format($kount)
. " TRANSACTIONS IN AN AVERAGE LAG TIME OF "
. number_format($days)
. " DAYS"
;

Select allOpen in new window

Let's see if that will help, ~Ray

thanks Ray, i'll give this a shot.

sorry about the error code oversight.  I reset some of my troubleshooting attempts back which would have changed the code lines.  My bad.

Tried this but got some errors with what I think may be errors with the returned data.

Here is the code:

<?php // RAY_temp_axessjosh.php
error_reporting(E_ALL);

/* COLUMNS IN QUESTION
trnsProcessed = auto timestamp when information was submitted by user
trnPrcDte = auto timestamp when we processed the information submitted.
*/

$mysqli = new mysqli('localhost', 'root', 'pw', 'db');
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}
date_default_timezone_set('America/chicago');
// THE STARTING AND ENDING BOUNDARIES
$new = date('c');
$old = date('c', strtotime('Today - 1 month'));

// MAYBE A USEFUL QUERY
$sql = "
SELECT
  trnsProcessed AS a
, trnPrcDte     AS z
FROM tblinfo1
WHERE ( trnsProcessed BETWEEN '$new' AND '$old' )
AND   ( trnPrcDte     BETWEEN '$new' AND '$old' )
"
;
// ASSUME MYSQL INSTEAD OF MYSQLI
$res = mysql_query($sql);
if (!$res) die ("FAIL: $sql BECAUSE: " . mysql_error());

// PROCESS THE RESULTS SET
$kount = 0;
$total = 0;
while ($row = mysql_fetch_assoc())
{
    // IF THE VALUES COME BACK AS DATETIME STRINGS, CONVERT TO SECONDS
    $a = strtotime($row['a']);
    $z = strtotime($row['z']);

    // COMPUTE THE DIFFERENCE IN SECONDS
    $d = $z - $a;

    // SUM OF SECONDS
    $total = $total + $d;

    // SUM OF MEASUREMENTS
    $kount++;
}

// AVERAGE NUMBER OF SECONDS
$avgs = $total / $kount;

// AVERAGE NUMBER OF DAYS
$days = $avgs / (60*60*24);

echo "WE PROCESSED "
. number_format($kount)
. " TRANSACTIONS IN AN AVERAGE LAG TIME OF "
. number_format($days)
. " DAYS"
;
?>

Select allOpen in new window

then the errors i get are as follows:

Warning: mysql_fetch_assoc() expects exactly 1 parameter, 0 given in C:\wamp\www\axessDB\newDB\admin_inc\portal-admin-functions.php on line 36

Warning: Division by zero in C:\wamp\www\axessDB\newDB\admin_inc\portal-admin-functions.php on line 53
WE PROCESSED 0 TRANSACTIONS IN AN AVERAGE LAG TIME OF 0 DAYS

Darn.  That's the trouble with trying to write data base queries when you don't have the data base to test.

Line 36:

while ($row = mysql_fetch_assoc($res))

Please give it another try and let me know if you get better results.  Thanks, ~Ray

thanks Ray,

I have tried to answer questions on here myself which has helped me (although I still struggle) to provide better information in my questions.

I put the update you made in and that fixed the first error, however I am still getting the Error Dividision by zero.  I went ahead and uploaded the file to our live server just to see if perhaps the test data i have was causing the error but that is not the case, I get the same error on the live server with real data as well.

I just checked the DB setup.  

TrnsDte is getting its value from a DB Timestamp of current_timestamp

chk processed is getting its value from MySQL INSERT with NOW() as follows:

$updateSQL = sprintf("UPDATE tblinfo1 SET trnsProcessed=%s, chkProcessed=%s, trnPrcDte=NOW() WHERE transID=%s",
                       GetSQLValueString($_POST['trnsProcessed'], "int"),
                       GetSQLValueString($_POST['chkProcessed'], "text"),
                       GetSQLValueString($_POST['transID'], "int"));

  mysql_select_db($database_con_portal_test, $con_portal_test);
  $Result1 = mysql_query($updateSQL, $con_portal_test) or die(mysql_error());

Select allOpen in new window

 
Could this be causing a conflict?