SELECT TIME_DIFF(time_entry, time_exit)
  FROM entry_exit
 WHERE customer = 'SOME CUSTOMER';
[download]

In other words, did you even look at the MySQL manual? http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

SELECT (SUM(exittime) - SUM(starttime)) / COUNT(*) 
AS average FROM cust_records;
[download]

In response to your update - your formula is wrong. You're looking for:

SELECT <total_time_in_system> / <number_of_times_in_system>
  FROM  . . .
[download]

So, given a table that looks something like:

mysql> describe timestamps;
SESSION   INT
CUSTOMER  INT
TIME      TIMESTAMP
ENTRY     TINYINT
. . .
mysql>
[download]

With the constraints
• (SESSION, ENTRY) is unique
• ENTRY is constrained to (0,1).
you could write SQL like

SELECT SUM(UNIX_TIMESTAMP(b.time) - UNIX_TIMESTAMP(a.time))
  FROM timestamps a, timestamps b
 WHERE a.session = b.session
   AND a.entry = 1
   AND b.entry = 0
;

SELECT COUNT( * )
  FROM timestamps
 WHERE entry = 1
;
[download]

Divide the first by the second, checking to make sure that the second isn't 0. You now have the average number of seconds any given login spent on your site.

Of course, I'd make sure that the appropriate indices were there. Maybe, an index on (SESSION, ENTRY, TIME) would be appropriate.

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

I shouldn't have to say this, but any code, unless otherwise stated, is untested

select time_diff(t1.timestamp, t2.timestamp)
  from the_table t1, the_table t2
 where t1.customer_id = t2.customer_id
   and ... other conditions ....
[download]

Michael

Yes, MySQL 4.x and higher supports self-joins. I'm not sure about 3.x.

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

I shouldn't have to say this, but any code, unless otherwise stated, is untested

select customer, avg(TIMEDIFF(dttmin, dttmout))
  from timetable
 group by customer;
[download]

Trek

Correcting myself...

I'm not so sure that the avg() function will work on timediff. My native DB is Oracle, and this sort of thing works there, but I can't vouch for MySQL.

Sorry if I led you astray...

Trek

The timestamp is in <yyyymmddhhmmss>format where the hour is in the 24 hour clock format

The average time formula should be (The total time spent by the customers in the system)/(Total number of customers) A few thoughts (more SQL-driven than Perl):

If your data isn't a date column, but holds a date value, you can reformat it, and use the MySQL UNIX_TIMESTAMP to get the number of seconds since 1/1/1970.

The reason that's useful is that if you convert your 'in' date and 'out' date and *then* subtract, you should be left with the total number of seconds between them.

Doing this, you can gain a huge time benefit by accepting that the mathematical order of precedence for addition and subtraction are equivalent, and you can get total seconds spent in the system by: (Total seconds of the converted in dates) minus (Total seconds of the out dates)

So:

A: Select SUM(UNIX_TIMESTAMP($date)) where record_type = 'I';

B: Select SUM(UNIX_TIMESTAMP($date)) where record_type = 'O';

C: Select COUNT(*)) where record_type = 'I';
[download]

Btw, you could easily combine A&C into one query, without making the query too hard to read... I left them separate to make it simple to read.

If you're worried about the SUM columns getting too large, then create a VIEW on the table which combines the two rows into one, then do your initial SUM on the subtraction of wach row of the view... Should get the same thing

Does that help? I know it's not a Perl answer, but it didn't really seem like a Perl question, either.

Trek

+----------+
| t1       |
+----------+
| 00:23:03 |
+----------+
1 row in set (0.00 sec)
[download]