time elapsed - PHP

On Thu, 28 Feb 2008 17:45:47 +0100, Jerry <Jerry@nospam.not> wrote:

> I have a MySQL table with two timestamp fields, in MySQL's default
> format, e.g. 2008-02-28 10:33:51
>
> How can I then compare the two in order to get the elapsed time between
> the two values? Does php (or MySQL) have any convenience functions to do
> that? Or do I have to start with strptime() and work it out from there?
>
> Thanks again to everybody for all the replies.

TIMEDIFF(), possibly combined with TIME_TO_SEC()
--
Rik Wasmus

On 28 Feb, 16:45, Jerry <Je...@nospam.not> wrote:
> I have a MySQL table with two timestamp fields, in MySQL's
> default format, e.g. 2008-02-28 10:33:51
>
> How can I then compare the two in order to get the elapsed time
> between the two values? Does php (or MySQL) have any convenience
> functions to do that? Or do I have to start with strptime() and
> work it out from there?
>
> Thanks again to everybody for all the replies.

In fact the format 2008-02-28 10:33:51 is not how MySQL stores
timestamps. That is just one format that it can use to display it.

Possibly DATEDIFF() or TIMEDIFF()

You'll find a good source of answers in the manual:
http://dev.mysql.com/doc/refman/5.0/...functions.html

Captain Paralytic wrote:

> In fact the format 2008-02-28 10:33:51 is not how MySQL stores
> timestamps. That is just one format that it can use to display it.
>
> Possibly DATEDIFF() or TIMEDIFF()

wonderful, thanks to both of you.

I've written a query to get the mean and the std deviation:

SELECT
SEC_TO_TIME(
AVG(TIMEDIFF(main.time_stamp, request.time_stamp))
)
AS average_time,
SEC_TO_TIME(
STDDEV_POP(TIMEDIFF(main.time_stamp, request.time_stamp))
)
AS std_dev
FROM main, request
WHERE main.ID = request.ID

Does anybody know of anything else in php or MySQL that is easily
available to toss in (for data interpretation)?

Jerry wrote:

> I've written a query to get the mean and the std deviation:
>
> SELECT
> SEC_TO_TIME(
> AVG(TIMEDIFF(main.time_stamp, request.time_stamp))
> )
> AS average_time,
> SEC_TO_TIME(
> STDDEV_POP(TIMEDIFF(main.time_stamp, request.time_stamp))
> )
> AS std_dev
> FROM main, request
> WHERE main.ID = request.ID

that contains a mistake: using SEC_TO_TIME

the output of AVG is in time but without colons, E.g., 1 minute
38 seconds comes out as 138 (not as 1:38)

So SEC_TO_TIME(value) should be replaced with TIME(value)