Solved

check for difference in dates, only choose last 30 days

Posted on 2013-01-02
22
289 Views
Last Modified: 2013-06-18
I'm trying to find the average difference between a submitted date and a date in which the item was processed.

I've setup my query as follows.

SELECT AVG(TIMESTAMPDIFF(HOUR,transDTE, trnPrcDte)) AS difference FROM tblinfo1

Open in new window


what I need to do now is only select those items for the past 30 days.  I'm sure this is done in a WHERE statement but can't figure out how to structure to find todays date, then go back 30.
0
Comment
Question by:axessJosh
  • 11
  • 7
  • 3
  • +1
22 Comments
 
LVL 8

Expert Comment

by:Barry62
ID: 38738304
something like:

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

Author Comment

by:axessJosh
ID: 38738320
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.
0
 
LVL 8

Expert Comment

by:Barry62
ID: 38738335
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
0
 
LVL 2

Author Comment

by:axessJosh
ID: 38738346
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.
0
 
LVL 8

Expert Comment

by:Barry62
ID: 38738370
Let me experiment a bit later tonight.  I'm at work, and I can't test it right now.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 38738437
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.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 38738479
Almost seems as though it's only taking days into account and not hours, mins, sec of the time stamp.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38739645
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.
http://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.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 38793467
Would there be a difference between finding the dates in PHP vs. at the server level in terms of speed and effeciency?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38793497
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.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 38793529
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38793561
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.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 38793580
Here is the setup of the table.

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

Open 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.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 38793639
This is how I might go about it.  Untested code, but you can probably use most of it.

<?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"
;

Open in new window

HTH, ~Ray
0
 
LVL 2

Author Comment

by:axessJosh
ID: 38793910
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
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38793995
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.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 38794012
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"
;
?>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38794091
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"
;

Open in new window

Let's see if that will help, ~Ray
0
 
LVL 2

Author Comment

by:axessJosh
ID: 38794100
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.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 38802717
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"
;
?>

Open 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
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38803767
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
0
 
LVL 2

Author Comment

by:axessJosh
ID: 38805675
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());

Open in new window

 
Could this be causing a conflict?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now