PHP question

[Sean]

If you want to do it in php.. =) you can.. the appended note [0]
calculates between two dates, so you would have to wrap it in a loop that
with a counter and total time then do the divide at the end. I doubt it is
the fastest solution but if you are only running a report once a month/day
it sure isnt going to kill anything.

Futuristically, you can add the date calculation code to the closed ticket
function and have it update a field with the total time in it when the
ticket is closed so you don't have to recalculate the total time
everytime you run the report.

If you really wanted to get spiffy with the code. You could use the
mcal functions to calculate the day of the week, and have it subtract
non-work hours such as saturday and sunday and anything outside of 8-5 or
whatever your typical working hours are so your averages don't get thrown
off by someone submitting a request at 4pm on friday and having it fixed
at 10am on Monday which on an 8-5 m-f job would realistically only be 3
working hours, but the real time (simple subtraction) method would yield
66 hours. If I did it this way I would defintely store this in the
database in its own field.

Sean


[0]
SOLUTION: Calculate the day difference between 2 dates

I have some dates stored in a MySQL database in the format 'yyyy-mm-dd'

I wanted to find the difference between those dates and the current date
in whole days.
Note: you could calculate the difference in months just as easily.


// Routine to display the difference between two dates

// Date stored in the MySQL database as yyyy-mm-dd

$today=date("Y-m-d 00:00");
$date1=strtotime( "2000-07-22 00:00" );
// The date value [2000-07-23] here would be the retrieved mysql string

$date2=strtotime($today);
echo "Difference: ".(($date2-$date1)/86400);
// Prints number of days difference








On Wed, 31 Oct 2001, Jeremy Bowers wrote:

> Mike Rambo, you said:
> > What I think I need:
>
> IMHO, the best thing to do here is *not* to do it in PHP... do it in
> the SQL queries.
>
> I'm not going to read the MySQL reference for these, but you should be
> able to do the following:
>
> > *count number of records completed in the last X number of days
>
> Should be a COUNT() function of some kind.
>
> > *select all records completed in the last X number of days
>
> You probably know this is a "SELECT [something] FROM [table] WHERE
> date > [date arithmatic... there's a NOW function and a date add
> function right in MySQL's SQL implementation, so use those]".
>
> > *determine days it took to complete each machine
>
> You should be able to compute that from each record, right?
>
> "SELECT machineId, DAYS(dateEnded - dateStarted) FROM [table]"
>
> Something like that. Again, you'll want to look up the DAYS function
> and the date manipulation functions in the MySQL manual.
>
> > *sum number of days for all machines
>
> "SELECT SUM([summed quantity]) FROM table WHERE ..."
>
> > *divide total days by number of machines in time period
>
> "SELECT SUM([summed quantity]) / COUNT([summed quantity]) WHERE ..."
>
> Don't recall all  the function names, but you should be able to do
> this in a two or three line query in SQL. (You *may* need a sub-SELECT
> statement inside the COUNT statement. I'd hope not. There may also
> already *be* an average function.)
>
> Let the database do the work... it'll do it faster.
>
>