MySql Timestamps

[Agyeya]

Greetings fellow Monks!
I have an MySql table, which records the entry and exit time of a customer in the system using a timestamp, which is automatically generated.
What I am confused with right now is that I have to find the average time that a customer spends in the system.
Since a timestamp includes both the date and time, I need to be able to subtract the two values (one from the timestamp of entry and the other of exit) and convert this into time.
Please assist me in this. I have been trying to get a proper alogrithm for this but am not being successful!
Hoping for a helpful reply
Agyeya Gupta
Update
Perhaps if i was not clear above, there are two records for each customer. One contains the timestamp for entry, and some other processing details. And the second record contains the timestamp for exit.
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)

[dragonchild]

Have you tried

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

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

[heroin_bob]

Also, make sure you timestamp is long enough so that you're getting as specific as your needs dictate. Here's some info on timestamp definitions if you're not familiar. As dragonchild suggested, the manual is always a good place to start.

Updated in response:
Something along these lines might help...


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


Thnx to dragonchild for pointing out my incorrect logic :)

[dragonchild]

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

SELECT <total_time_in_system> / <number_of_times_in_system>
  FROM  . . .

So, given a table that looks something like:

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

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
;

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.

[mpeppler]

If the entry and exit timestamps are in different rows, then you simply need to do a self-join to get both. Something like:

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

Note that I'm not sure if MySQL supports this form of joins, or if you need to use ANSI joins.

Michael

[dragonchild]

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

[TrekNoid]

Something like:

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

Should get you pretty close.

Trek

[TrekNoid]

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

[TrekNoid]

Per your update:

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';

Then, (A - B) / C gives you the value you're looking for, in seconds, right?

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

[Anonymous Monk]

I likewise had this problem. I have 2 columns fst_response, and time_in. I wanted to calculate the average response time.
Because avg() does not work with mysql's time format you need to convert to and from seconds:

mysql> select sec_to_time(avg(time_to_sec(timediff(fst_response,time_in)))) as t1 from cases;

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